Interview Query

How to Use the OUTER JOIN in SQL (With Example Syntax)

SQL is often the language of choice for storing, manipulating and retrieving data from a database for data science projects. An OUTER JOIN is a SQL command that makes handling data much more efficient.

In SQL, you can use an INNER JOIN to pull common rows between two tables. But what if you want to get the unique rows or the rows that are not common between the two tables?

In these cases, you need the opposite of an INNER JOIN, which is the OUTER JOIN. Let’s take a closer look at how to use the OUTER JOIN.

-> See our guide to SQL Interview Questions to further your SQL practice.

What Is an OUTER JOIN?

image

The INNER JOIN command extracts the intersection between two tables.

INNER JOIN syntax
SELECT colums
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

The OUTER JOIN does the opposite, and extracts the rows unique to only one table. To do that, you can use two JOIN commands, OUTER and RIGHT/ LEFT, depending on which table we want to extract unique rows from.

The OUTER JOIN command returns the rows from both tables.

OUTER JOIN syntax
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

The FULL keyword here indicates that we want all rows in both tables.

How to Pull Unique Rows with OUTER JOIN

Pulling unique rows from one table without rows that exist in both tables is an intermediate SQL task. One way to do that is to get the rows from the table we want, and then filter the results to remove any rows in the other table using the WHERE keyword.

To pull unique rows, start with an OUTER JOIN command with the LEFT or RIGHT keywords.

image

LEFT OUTER JOIN

Here is the syntax to use for extracting unique values from the left table:

SELECT t1.*
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.id=t2.id
WHERE t2.id IS null

RIGHT OUTER JOIN

You can do the same but with the right table by changing the WHERE command’s condition in the last line and using the RIGHT JOIN command instead of LEFT.

Here is the syntax to use for extracting unique values from the right table:

SELECT t1.*
FROM table1 t1
RIGHT OUTER JOIN table2 t2
ON t1.id=t2.id
WHERE t1.id IS null

Learn SQL with Interview Query

The best way to get better at data manipulation in SQL is practice. Interview Query offers a variety of SQL learning resources to help you practice and improve your SQL skills, including: