Advanced Joins: Left, Right, and Full Outer Joins

In this post, we learn more about Joins. We learn about advanced joins such as the left, right and full outer join and how we can use this in our projects.

Learning Objectives:

• Explain how left, right and full outer joins work.
• Identify situations to use each type of join.
• Use each type of join to combine data from multiple tables.

Left Joins.

This type of join returns all records from the left table and matching records from the right table. The result will be NULL if there is no match.

You can visualise the idea much better by looking at this diagram above.

Let's say that we have in ‘Table A’, a list of customers. And in ‘Table B’ we have a list of orders. If we tried to use an inner join, which we learned about in my previous post. Then we would end up missing a lot of data on customers that have not placed an order.

However, when we use the left join. What we are saying is we want all the data in ‘Table A’ (customers) and if they happened to have placed an order. That is great, please bring in that data from ‘Table B’ (orders). If they have not, a NULL value will be placed next to the corresponding customer.

Here is an example:

SELECT C.CustomerName, 
O.OrderID
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerName;

What is this code querying?

We want the ‘CustomerName’ from the ‘customers’ table and the ‘OrderID’ from the ‘orders’ table. And then we are going to retrieve this from the ‘customers’ table as defined by our FROM and LEFT JOIN statements.

Then we use the ‘LEFT JOIN’ to join the ‘orders’ table onto the ‘customer’ table where ‘C.CustomerID’ and ‘O.CustomerID’ is the column we want to join.

Right Joins.

Let’s look at Right Joins and see what this join does to our tables and data. Once again, let's imagine that ‘Table A’ is our ‘customers’ table and ‘Table B’ is our ‘orders’ table.

This is what a ‘Right Join’ looks like.

This would return all records from the right table, and the matched records from the left table regardless of whether there is an associated customer to order. Again, a NULL value is used from the left side when there is no match.

The table you list first in your query is the table that is acted upon when defining the type of join you want to use.

Right Join Example:

The following statement returns all employees and any orders they might have placed:

SELECT Orders.OrderID,
Employees.LastName,
Employees.FirstName

FROM Orders

RIGHT JOIN Employees ON 
Orders.EmployeeID = 
Employees.EmployeeID

ORDER BY Orders.OrderID

The difference between a LEFT and RIGHT join is the order of the tables listed in the query. Left joins can be turned into right joins by reversing the order of the tables.

Full Outer Joins.

This type of join simply returns all records whether there is a match in either the left or right table.

A ‘Full Outer Join’ returns all the data whether it is matching or not.

Full Outer Join Example:

This statement selects all customers and all orders.

SELECT Customers.CustomerName,
Orders.OrderID

FROM Customers

FULL OUTER JOIN Orders ON
Customers.CustomerID = 
Orders.CustomerID

ORDER BY Customers.CustomerName;

All the joins follow a similar syntax.

I would also love to connect on Twitter, LinkedIn or on my Website. I am not sure what value I could provide you with but if you reach out, I would be more than happy to have a conversation or talk about what I have written here.