How to Keep Unmatched Rows When You Join two Tables in SQL

Learn how to use JOIN to keep both matched and unmatched rows when you join two tables.

Joining two or more tables is a skill you need a lot if you’re working with databases. To review and practice your SQL joining skills, I recommend the interactive SQL JOINs course. It contains over 90 exercises and sections on different joining challenges.

If you often join tables in SQL, you’ve probably noticed not all data from one table corresponds to data from another table all the time. Sometimes, you need only the data that have a match in both tables. And sometimes, you also need to keep the unmatched rows. How do you do that in a JOIN?

Which JOIN Gets you the Unmatched Rows?

You probably know this already, but it’s worth repeating. The purpose of the JOIN is to get the data from two or more tables. You join them together by the column(s) they have in common.

The four main types of JOINs are:

• (INNER) JOIN.
• LEFT (OUTER) JOIN.
• RIGHT (OUTER) JOIN.
• FULL (OUTER) JOIN.

When you use a simple (INNER) JOIN, you’ll only get the rows that have matches in both tables. The query will not return unmatched rows in any shape or form.

If this is not what you want, the solution is to use the LEFT JOIN, RIGHT JOIN, or FULL JOIN, depending on what you’d like to see.

If you’re new to the JOIN, here’s where every JOIN type is thoroughly explained. You can also have a SQL JOIN Cheat Sheet close by to help you with the JOIN syntax and use.

Get to Know the Data

The data we’ll be working with consists of two tables:

• employee.
• project.

The table employee has four columns:

• id: The employee’s ID; it is the primary key (PK) of the table.
• first_name: The employee’s first name.
• last_name: The employee’s last name.
• project_id: The ID of the project and the foreign key (FK) of the table, referencing another table called project.

Here’s what the data from the table employee looks like:

There are two columns in the table project:

• id: The ID of the project; it is the primary key (PK) of the table.
• project_name: The name of the project.

And this is the data from the table:

What Happens When you Use the INNER JOIN?

To get the employees’ name and the projects they are working on by using the INNER JOIN, you’ll have to write this code:

SELECT
  first_name,
  last_name,
  project_name
FROM employee e
JOIN project p
  ON e.project_id = p.id;

Note: Of course, you can use either JOIN or INNER JOIN as the keyword – they are the same.

Here’s what you get as a result:

The result shows only the rows from the table employee that match the rows in the table project. In other words, the code returns only the employees that have a project allocated to them. This, however, means each table may have rows that do not correspond to the other table. There may be some employees who are not allocated to any projects, and there may be some projects without any employees allocated to them.

How do you also get the unmatched rows?

Get all the Matched and Unmatched Rows From one Table

To get all of the rows from just one of the tables – the matched rows as well as the unmatched rows – you need to use the LEFT JOIN or the RIGHT JOIN. Which one you should use depends on which table you want to keep the unmatched rows from. The LEFT JOIN will do that from the left-hand table, the RIGHT JOIN from the right-hand one. Let me show you what that means in practice.

Using the LEFT JOIN

To get the matched as well as unmatched rows from a table using the LEFT JOIN, you’ll have to write this code:

SELECT
  first_name,
  last_name,
  project_name
FROM employee e
LEFT JOIN project p
  ON e.project_id = p.id;

It’s almost the same code as in the previous example. The only difference is it uses the LEFT JOIN keyword instead of the JOIN. This will get you all the data from the left-hand table (employee, in this case), and all the matching data from the right-hand table (project). When there is no matching data in the table project, you get the NULL values., but you still get all the values from the table employee. See for yourself:

How do you interpret this data? You’ve got all the employees and the projects they are working on. When there’s a NULL value in the column project_name, it means the employee in question is not working on any project.

Using the RIGHT JOIN

Now, let’s write the same code with the RIGHT JOIN instead of using the LEFT JOIN.

SELECT
  first_name,
  last_name,
  project_name
FROM employee e
RIGHT JOIN project p
  ON e.project_id = p.id;

It’s a mirror image of the previous code. It will return all the data from the right-hand table, project, and only the matching rows from the left-hand table, employee. Again, when there is no matching data from the table employee, the values will be NULL.

This is the query result:

Now, this data shows all the projects that exist in the table project. When the columns first_name and last_name are NULL, it means there is no employee working on that project.

Will the FULL JOIN Get you all the Matched and Unmatched Rows?

I think the query using the FULL JOIN and its result will speak for itself. Let’s see what it does. First, the query:

SELECT
  first_name,
  last_name,
  project_name
FROM employee e
FULL JOIN project p
  ON e.project_id = p.id;

Again, the only difference from the previous queries is the type of join used. Everything else is the same. This FULL JOIN query will return the following data:

Let’s inspect the data a bit. There are rows that are found in both tables. There are, however, rows from the table employee that have no corresponding rows in the table project. You can identify these rows by the NULL values in the column project_name. There are also rows from the table project with no matching rows in the table employee. Again, this is where the NULL values are. This time, the NULLs will be in the columns first_name and last_name.

When to Use Which JOIN

Now that you have seen what each JOIN returns, let’s clarify what each JOIN does.

The JOIN or INNER JOIN does not return any non-matching rows at all. It returns only the rows that match in both of the tables you join. If you want to get any unmatched rows, you shouldn’t use it.

The LEFT JOIN and the RIGHT JOIN get you both matched and unmatched rows. However, you need to be aware from which table you get the unmatched rows. Based on your needs, you’ll use either the LEFT JOIN or the RIGHT JOIN. They both get you all the rows from one table (matched and unmatched) and all the matching rows from the other table. When no matching rows are found in the other table, the columns from the other table show NULL values.

The FULL JOIN gets you all the data from all the tables you join. It’s like combining all three joins above. You get all the matching rows like when using the INNER JOIN. You’ll also get non-matching rows from the left-hand table and from the right-hand table, also. It is as if you used the LEFT JOIN and the RIGHT JOIN simultaneously.

Here are some more examples for practicing joining the tables.

Learn the Different Types of JOIN to Select the one you Need!

You see, knowing how these JOINs work is quite useful. They allow you to join two or more tables and get different combinations of data from every table. There’s no difference in how you write the code. The only thing that changes is the keyword you use depending on the type of JOIN you need. It is really important to understand what data each of these JOINs return.

To master and practice this skill, the SQL Basics course and the SQL JOINs course are what you need! If you need advice on how to practice JOINs, here are some tips.