SQL Joins Part 1

In SQL, there are three different types of JOINs, which, if mastered, can be extremely helpful in querying your data.  The three different joins are:

1. OUTER JOIN
2. INNER JOIN
3. CROSS JOIN

First, with any JOIN statement, you need to specify the columns you will join on.  This tells SQL which columns to use as comparison.  After our second table, we put the word "on" and follow that with our columns comparisons.  As we jump into this post, we will see the "on" used in our FROM clause.

Today I will focus on the OUTER Joins.

Within the OUTER Joins there are the LEFT, RIGHT and FULL JOINs.  These joins are pretty easy to understand.  A query containing a LEFT or RIGHT JOIN will return all rows in the described table (left or right) and matched rows in the other table.  This is defined by which table is on the left (the first table in your FROM clause) or on the right (the second table).  Here is an example if we are using our Customers and Orders tables:

SELECT *

FROM Customers 

LEFT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

In this example, Customers is on the left, while Orders is on the right.  This will return all rows in the Customers  table and any matching data in the Orders table.

We can then reverse it for another example:

SELECT *

FROM Customers 

RIGHT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

The above will return all rows in the Orders table and any matching rows in the Customers table.  You can also get the same results by running the following:

SELECT *

FROM Orders

LEFT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

Notice that Orders is the "left" table and Customers is the "right" table.  It may become more evident why you would use LEFT or RIGHT when we reference more than one table.  Let's take our example and add OrderDetails into the mix:

SELECT *

FROM Customers 

LEFT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

LEFT JOIN OrderDetails on Orders.OrderNumber = OrderDetails.OrderNumber

This query gives us all Customers with matching rows in the Orders table and then all OrderDetails matching with the Orders table.  We could accomplish the same by running the following:

SELECT *

FROM OrderDetails 

RIGHT JOIN Orders on Orders.OrderNumber = OrderDetails.OrderNumber

RIGHT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

We could also mix it up:

SELECT *

FROM OrderDetails 

RIGHT JOIN Orders on Orders.OrderNumber = OrderDetails.OrderNumber

LEFT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

This would give all rows in the Orders table with matching rows in the OrderDetails and Customers table.

A FULL Join will return all rows from both tables.  Any row from one table that doesn't have a match in the other table will return all NULLS for the other table.

SELECT *

FROM Customers

FULL JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

To read up more on SQL Join, go here: Using Outer Joins