One of the most difficult concepts for me as I was learning database syntax was joins. Of course, I tried reading and understanding articles and books that dealt with this topic. In almost every case, I was faced with the venn diagram examples.
Perhaps it’s just me, but it didn’t exactly translate into understanding how tables are related. Maybe I’m too literal. Database tables aren’t circles. Database tables are rectangular.
Here’s how I finally made sense of it for myself many years ago. Suppose we have two tables, customer and order.
In the customer table, the primary key (the unique identifier for the row) is CustomerID. In the order table, the primary key is OrderID and the foreign key (used to connect the two tables) is CustomerID.
If we want to see the rows that match, we will use an inner join. In other words, we will get customers that have placed orders. Another way to express it is to say we get orders that were placed by customers. We get the rows that have equal CustomerID values.
The struggle for most people in understanding joins comes with outer joins. There are three types of outer joins: left outer join, right outer join, and full outer join. These join types give me the results of the inner join plus the outer bounds of one or both tables.
Here is the syntax (Note that “left join” could also be written as “left outer join”). These are equivalent.
Customer c left join order o on c.customerid = o.customerid
If we think about the table that is to the left of the words “left join,” this represents the table from which we will get all of the records (or rows).
We get all customers regardless of whether they have or have not placed an order. Perhaps we have customer prospects that are in the customer table, even though they haven’t placed an order. We want a complete list of customers and if they have placed an order, we want to see it. The only orders we see are those that have a matching customer. However, we see every customer even if they have no orders.
If we think about the table that is to the right of the words “right join,” this represents the table from which we will get all of the records (or rows).
We get all orders regardless of whether they were or were not placed by a customer. Perhaps we have other entities that can order – like employees. We want a complete list of orders and if they were placed by a customer, we want to see it. The only customers we see are those that have a matching order. However, we see every order even if they have no associated customer.
The full join is a complete listing of both tables. We get all customers regardless of whether they have or have not placed an order. We get all orders regardless of whether they were or were not placed by a customer. If the CustomerIDs match, we see that. If they don’t, we see that as well.
Realted Courses:
Writing Queries Using Microsoft SQL Server 2008 Transact-SQL (M2778)
Querying Microsoft SQL Server 2012 (M10774)
Administering Microsoft SQL Server 2012 Databases (M10775)
Designing and Implementing a SQL Server 2008 R2 Database (M6232)