How to Keep Unmatched Rows From Two Tables in a SQL JOIN

Are you looking to join two tables in SQL without removing unmatched rows? Do you want to keep unmatched rows from one or both tables? In this article, I’ll explain how to keep all the records you want by using outer JOINs, such as LEFT JOIN, RIGHT JOIN, and FULL JOIN. Examples included!

The SQL JOIN is a powerful tool that helps you combine data from multiple tables of your database. This is a core idea behind relational databases – storing data across different interrelated tables and combining data from these tables when needed for data analysis and reporting. If you need a recap on joining tables in SQL, check out this beginner-friendly guide and our interactive SQL JOINs course.

Unfortunately, when you are only starting with SQL, JOIN queries may produce frustrating results. For example, you may get duplicates, missing data, unexpected NULL values, etc. In this article, I want to focus on the case in which you want to keep unmatched rows from two tables but a SQL JOIN removes them. We’ll see why this happens and how to get the output you expect.

Let’s start with an example.

Say you are planning a marketing campaign where you give a special bonus to a set of your customers. There are several criteria for a customer to be eligible for the bonus. For this particular campaign, you look for customers who have orders with the status “Completed” from the last month but have not won in your previous marketing campaign.

You want to combine information from the following three tables:

You may use the following query to join these tables and get a list of customers with the additional information you need:

SELECT 
  c.id, 
  c.first_name, 
  c.last_name, 
  o.order_status, 
  lcp.winner
FROM customers c
JOIN orders o
ON c.id = o.customer_id
JOIN last_campaign_participants lcp
ON c.id = lcp.customer_id;

If you are not sure how this query works, check out our interactive course with 93 coding challenges covering different types of JOINs.

Here’s the output of the SQL query above:

As you see, the output includes only two out of four customers. This is because only these two customers have corresponding records in all three tables, and JOIN or INNER JOIN outputs only the matched rows. With this output, we see only one eligible customer for the next campaign – Kate Wilson (ID 101). The other one was the winner of our last campaign and thus not eligible for this campaign.

But can we miss somebody by removing the unmatched rows? Actually, yes. We can overlook customers who have completed orders but did not participate in the last campaign at all. These customers are missing from this table. We want all customers in the output of the SQL JOIN, then filter the results as needed.

Luckily, SQL has outer JOINs that allow you to keep unmatched rows from two tables.

Inner JOINs vs. Outer JOINs

In contrast to INNER JOIN, or just JOIN, which only returns matched rows from two tables, outer JOINs also return the unmatched rows in SQL. There are several types of outer JOINs:

• A LEFT JOIN returns all the records from the left (first) table even if there are no matches in the right (second) table.
• A RIGHT JOIN returns all the records from the right (second) table even if there are no matches in the left (first) table.
• A FULL JOIN returns all the records from both tables, including unmatched ones from either table.

This article explains these SQL JOIN types in more detail with examples and illustrations. Also, see this great SQL JOINs Cheat Sheet to get all the nuances of the syntax.

To use an outer JOIN, you just replace the JOIN keyword with the LEFT JOIN, RIGHT JOIN, or FULL JOIN keyword depending on your case. However, note that for LEFT JOIN and RIGHT JOIN, the order of tables in the SQL query matters.

Now, let’s modify our first query to keep unmatched rows and get all customers in the output. One option is to use LEFT JOIN while making sure that the customers table is listed first in the query (i.e., right after the FROM keyword):

SELECT 
  c.id, 
  c.first_name, 
  c.last_name, 
  o.order_status, 
  lcp.winner
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
LEFT JOIN last_campaign_participants lcp
ON c.id = lcp.customer_id;

The output of this query includes a list of all current customers with the corresponding additional information from the two other tables. Note that if no records match the customer from the left table, these records are still included but with NULL values in the respective columns:

By keeping the unmatched rows with a LEFT JOIN, we spot another customer who is eligible for the campaign. Specifically, Maria White (ID 102) has completed orders and did not participate in the last campaign – thus obviously did not win.

To understand outer JOINs further, let's see a few more examples.

Outer JOIN Examples

Now, imagine we run a bookstore. We want to compare two groups of customers: those who bought Harry Potter and the Philosopher’s Stone and those who bought Harry Potter and the Chamber of Secrets. Are these the same people? Is there anyone who bought one book but not another? We may want to recommend the other book to them.

Example With LEFT JOIN

First, we want to see everyone who purchased Harry Potter and the Philosopher’s Stone from us and check whether they have also purchased Harry Potter and the Chamber of Secrets.

To get this output, we use LEFT JOIN with a list of those who bought the first book included in the FROM clause of the query (left table).

SELECT 
  ps.product_id, 
  ps.first_name, 
  ps.last_name, 
  cs.product_id, 
  cs.first_name, 
  cs.last_name
FROM philosophers_stone ps
LEFT JOIN chamber_of_secrets cs
ON ps.customer_id = cs.customer_id;

This lets us keep all the buyers of Harry Potter and the Philosopher’s Stone even if there are no matched records in the second table:

Now, we see that two customers purchased the first book of the series but not the second. We may recommend the second book to them in our next marketing campaign.

Example With FULL JOIN

But what if there are customers who have purchased only the second book but not the first one? This would also be nice to know.

Instead of writing a separate query to spot this group of customers, it is better to use FULL JOIN that provides us with the whole picture: those who purchased only the first book, those who purchased only the second book, and those who purchased both books:

SELECT 
  ps.product_id, 
  ps.first_name, 
  ps.last_name, 
  cs.product_id, 
  cs.first_name, 
  cs.last_name
FROM philosophers_stone ps
FULL JOIN chamber_of_secrets cs
ON ps.customer_id = cs.customer_id;

Looks great! Now we can easily compare two groups of customers and find opportunities for good book recommendations. Outer JOINs can be super handy!

Time to Practice SQL JOINs!

Joining tables is one of the fundamental skills required for the effective use of SQL in data analysis and reporting. It is not that difficult, but you need a lot of practice with SQL JOINs to avoid pitfalls like missing records and unexpected duplicates.

I recommend starting with the SQL JOINs interactive course. It covers all key types of JOINs, joining a table with itself, joining multiple tables in one query, and joining tables on non-key columns. Get more details about this course in this overview article.

Bonus. Here are the top 10 SQL JOIN interview questions with answers.

Thanks for reading, and happy learning!