One of the most common tools we use when querying SQL Server databases is the JOIN operation. There are many different kinds of JOIN operations in SQL Server, including:
In this very brief tutorial, we’ll discuss a fifth type of JOIN: The FULL OUTER JOIN. This is another very helpful type of JOIN operation you should understand if you want to become a querying master.
We’ll discuss these topics in this tutorial:
- What is a JOIN anyway?
- How the FULL OUTER JOIN works
- Tips and trick
Also, you should consider downloading the following FREE Ebook:
FREE Ebook on SQL Server JOIN Operations!
This guide contains all the key points you need to understand about the different JOIN operations in Microsoft SQL Server. It will definitely be a great resource to keep and reference throughout your career as a data professional. Download it today!
Let’s get into it.
1. What is a JOIN anyway?
The JOIN operation (in all it’s various forms) is probably the most widely used querying tool available to us in Microsoft SQL Server. I dare say you will write more queries that do use a JOIN than queries that don’t us a JOIN.
In order to understand how the FULL OUTER JOIN works, you really need to understand how a JOIN works in the first place.
The JOIN operation allows us to retrieve data from two or more tables in a single query.
If you understand how an INNER JOIN works, and how a LEFT or RIGHT JOIN works, you will have an easy time understanding how the FULL OUTER JOIN works.
We’ll briefly go over the INNER, LEFT, and RIGHT JOINs in this tutorial, but you should take a look at the full beginner-friendly tutorials to learn more:
-
INNER JOIN in SQL Server: The Ultimate Guide for Beginners
-
LEFT JOIN in SQL Server: A Beginners Guide
-
RIGHT JOIN in SQL Server: Explained for Beginners
Setting up some data
We need to set up some data to work through some examples. Let’s say we own a bookstore, and we use a database to store information about our Books, Customers, and Orders:
Here are the CREATE TABLE and INSERT statements so you can create this data in your environment and follow along:
CREATE TABLE Books
(
BookID INT IDENTITY(1,1),
Title VARCHAR(50),
Author VARCHAR(20),
Price DECIMAL(5, 2)
)
INSERT INTO Books (Title, Author, Price)
VALUES ('As a man thinketh', 'Allen', 14.95),
('Eat that frog!', 'Tracy', 12.45),
('T-SQL Fundamentals', 'Ghan', 109.45),
('American Fire', 'Hesse', 24.00),
('Atomic Habits', 'Clear', 11.98),
('Pro SQL Server Internals', 'Korotkevitch', 65.99),
('Deep Work', 'Newport', 14.99)
CREATE TABLE Customers
(
CustID INT IDENTITY(5,5),
FirstName VARCHAR(20),
LastName VARCHAR(20)
)
INSERT INTO Customers (FirstName, LastName)
VALUES ('Doug', 'Jones'),
('Vanessa', 'West'),
('Gail', 'Shapiro'),
('Richard', 'Castone'),
('Abbey', 'Normal')
CREATE TABLE Orders
(
OrderID INT IDENTITY(100,1),
CustID INT,
BookID INT,
OrderDate DATE
)
INSERT INTO Orders (CustID, BookID ,OrderDate)
VALUES (5, 4, '2021-04-01'),
(5, 3, '2021-04-06'),
(10, 2, '2021-04-23'),
(20, 6, '2021-04-30'),
(20, 4, '2021-05-06'),
(15, 5, '2021-05-15'),
(55, 17, '2021-05-17')
Very good.
2. How the FULL OUTER JOIN works
I’ve said that if you understand how an INNER JOIN works, and how the LEFT or RIGHT JOIN works, you will have an easy time understanding how the FULL OUTER JOIN works.
Let’s quickly talk about each type of JOIN with examples.
INNER JOIN: Retrieves data where there is a match in both tables
Take a look at the following query that performs an INNER JOIN between the Orders and Books tables:
Each BookID we’re seeing in this result set is seen in both the Orders table and the Books table.
For example, take a look at this row in the Orders table:
The BookID for this row is 17. Is there a book in the Books table that has an ID of 17?
Nope.
Therefore, this particular row from the Orders table is not pulled into the result set of our INNER JOIN.
While we’re at it, take a look at this row in the Books table:
Is there a row in the Orders table that references a book ID of 7?
Nope!
This is also why we don’t see any details for this row in the result set of our INNER JOIN query.
Again, when it comes to INNER JOIN operations, the values we’re matching on need to exist in both tables. Here is a helpful diagram you’ll often see that describes the final result set of an INNER JOIN operation:
LEFT OUTER JOIN: Retrieves all data from the left table and only matching data from the right table
Let’s change the query to use a LEFT OUTER JOIN instead:
The LEFT OUTER JOIN will pull everything from the left table, regardless of if there is a match in the right table. The Orders table is our left table, and the Books table is the right.
From the right table (Books), we only display details if there is a match in the left table.
So we understand why we see the row with BookID 17 now, right?:
We’re pulling everything from the left table, even if there is no match in the right table. Book ID 17 is one of those books in the left table that simply has no match in the right table.
From a bookstore business perspective, this isn’t a good thing, right? We’re saying we sold a book that doesn’t exist in our inventory? Someone f*cked up.
Since there is no corresponding ID in the Books table, what is SQL Server supposed to use for the columns in our SELECT list that are from the Books table (namely the Title and Author columns)?
Well, NULL represents the absence of information, which is exactly what we have here.
Finally, we understand why we don’t see any references to this “Deep Work” book still, right?
Book ID 7 does not have a match in the right table. Remember, with a LEFT OUTER JOIN, we only display matching data from the right table.
Here’s a helpful diagram to understand the content returned from a LEFT OUTER JOIN:
RIGHT OUTER JOIN: Retrieves all data from the right table and only matching data from the left table
Folks, the RIGHT OUTER JOIN is the same idea, just flipped:
Ok, so a RIGHT OUTER JOIN will pull all content from the right table, regardless of if there is a match on the left table.
We’re already familiar with the book Deep Work and how it doesn’t have any corresponding rows in the Orders table. But it looks like there was another book without any references, As a Man Thinketh.
And again, since there is no match for these books in the Orders table, what is SQL Server supposed to use for the columns from the Orders table in our query? Answer: NULL
Here’s another diagram to help you understand the RIGHT OUTER JOIN:
FULL OUTER JOIN: Retrieves all data from the left table and all data from the right table
Folks, the FULL OUTER JOIN basically combines the logic of the LEFT OUTER and RIGHT OUTER JOINs. It returns all matching rows, and all rows from the left table (regardless of if there is a match in the right table), and all rows from the right table (regardless of if there is a match in the left table).
Here’s the diagram (I call this one Deep Purple):
And here’s an example:
So we see all content from Orders, and we see all content from Books. Period.
From the left table (Orders), we already know there is an order that references a book that does not exist:
And from the right table (Books), we already know there are two items without a reference in the left table. That’s As a Man Thinketh and Deep Work:
Since we’re using a FULL OUTER JOIN, we see all that stuff in one query.
And as we know, for the rows where there isn’t a match in the other table, NULL is used in the appropriate columns.
3. Tips and tricks
Here is a list of some tips and tricks you should know about the FULL OUTER JOIN.
Tip #1: The word ‘OUTER‘ is optional
If you write your queries with just ‘FULL JOIN‘, SQL Server knows you want to do a FULL OUTER JOIN. The two are the same thing and can be used interchangeably. This is actually true for all ‘OUTER‘ joins. I will usually see the word ‘OUTER‘ omitted simply because it’s less to write!
Tip # 2: If you understand the LEFT JOIN, you already understand how the RIGHT JOIN and FULL JOIN work
Folks, there’s a reason why the two most important JOIN operations you need to know are the INNER JOIN and the LEFT JOIN.
If you understand the LEFT JOIN, you really already understand the RIGHT JOIN because it’s the same logic, just flipped.
And if you know how the LEFT and RIGHT JOINs work, you understand how FULL JOIN works because it’s just the combination of the two!
Tip #3: The “IS NULL” predicate can be helpful
What if we specifically wanted to see orders that reference a book that does not exist? Well, we could run our LEFT JOIN query and filter the results to return only rows where the Title column (or Author column) is specifically NULL:
On that same idea, what if we wanted to see all books that don’t have a reference in the Orders table? (In other words, books that nobody is buying). We could use a RIGHT JOIN and filter the results where the OrderID (or any column from the Orders table) is NULL:
And of course, if we wanted to combine this information into a single query, we could use a FULL JOIN. We’ll set up the WHERE clause to return any orders that reference a non-existent book or any book that has never been ordered:
Superb!
By the way, do you know why you need to use “IS NULL” and not simply “= NULL”?
Next Steps:
Leave a comment if you found this tutorial helpful!
Don’t forget to download your FREE Ebook:
FREE Ebook on SQL Server JOIN Operations!
This guide contains all the key points you need to understand about the different JOIN operations in Microsoft SQL Server. It will definitely be a great resource to keep and reference throughout your career as a data professional. Download it today!
Do you know How to write an UPDATE statement with a JOIN? Check out the tutorial to find out!
If you need a full rundown on the first type of JOIN, which is the INNER JOIN, take a look at the full beginner-friendly tutorial here:
INNER JOIN in SQL Server: The Ultimate Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
