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. A SQL OUTER JOIN is a command that makes handling data much more efficient. And because of this SQL interview questions are common in data science interviews.

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.

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.

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.

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

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:

• SQL Data Science Course
• 500+ Data Science Interview Practice Questions
• VLOOKUP in SQL
• SQL Fuzzy Matching Guide
• Using SQL COUNT() with CASE WHEN
• Weighted Average SQL
• SQL Conditional Join
• SQL Cumulative Sum