Selecting Data from Multiple Tables

Parent Tables vs Child Tables

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

This video explains the differences between a Parent Table and a Child Table.

Keywords

  • T-SQL
  • Tables
  • Relationships
  • Parent Tables
  • Child Tables
  • Primary Key
  • Foreign Key

About this video

Author(s)
John Deardurff
First online
05 April 2019
DOI
https://doi.org/10.1007/978-1-4842-4905-5_3
Online ISBN
978-1-4842-4905-5
Publisher
Apress
Copyright information
© John Deardurff 2019

Video Transcript

Welcome to this video segment on parent tables versus child tables. In our previous video segment, we discussed how our relationships defined our parent table and our child table. As a quick review, let’s look at these two tables, Customers and Orders. If all I knew about these tables were their table names, I could probably identify the field that would connect those two tables. In this case, Customer ID would most likely be the field that would connect customers to orders. So which table would best describe Customer ID, the Customers table or the Orders table?

Well, the Customers table would tell me the last name, the first name, and the address, where the Orders table would only tell me the customer who placed that order. So in that case, the Customers table would be the best describer of the field I was connecting on. That would establish it as the parent table in the relationship and the Orders table as the child table in the relationship.

Why does this play a factor with our joins? It’s because when we’re looking at our joins, the parent table is on the left and the child table is on the right. So when I’m writing my join types, I have the option of selecting an inner join, a left outer join, and a right outer join that’s dependent on our parent table and our child table. So by default, if I don’t specify join type, it defaults to an inner join, and this would show us all the customers who have placed an order. It would not show us any customers who had not placed an order, or it would not show us any orders that did not have a matching customer.

Now left outer join is going to show us all the customers whether they placed an order or not. Specifically, it’s going to return all the records from the parent table, or the left table, as well as any that match.

Opposite of that we have the right outer join. A right outer join is going to show me all the orders with or without a customer. So if for some reason there were orders that did not have a matching customer, that would be called an orphan record. We’ll talk more about orphan records when we discuss the right outer join in a later video.

In this video segment, we have discussed the differences between our parent table and our child table, and how they work with our three main join types.