Subscriber only lesson.
Sign up to this course to view this lesson.
Full Outer Joins allow us to pull data from more than one table so long as their is a match in either table.
When to use
Use it when you have matching data in at least one of the tables.
SELECT [Last Name], [Blue Widget]
FULL OUTER JOIN [dbo].[Orders] ON [Current_Customers].[Customer ID] = [Orders].[Customer ID]
This will display the last name and number of blue widgets purchased by each customer ID.
Unlike INNER JOIN which required both tables to have the same thing in common (e.g. Customer ID), with OUTER JOIN only one table needs to have the thing that you designate (e.g. Customer ID) in order to work.
This means if you have a customer who has a customer ID in the Current Customers table, but not the Orders table, they will still show up in your results. They will just have NULL values in the Blue Widgets column, as they have not made any orders.
Hints & tips
- Full Outer Joins pull data when there is a match in at least one table.
Lesson notes are only available for subscribers.