In Lesson 5, we learned how to use INNER JOINs to combine data from multiple tables. INNER JOIN only returns records when there is a match in both tables. But what if you want to see ALL records from one table, even when there's no match in the other table?

This is where OUTER JOINs become essential.

What are OUTER JOINs?

OUTER JOINs return all records from one table (or both tables), and the matching records from the other table. When there is no match, the result will show NULL values for the missing data.

There are three types of OUTER JOINs:

✅ LEFT JOIN - Returns all records from the left table, and matching records from the right table
✅ RIGHT JOIN - Returns all records from the right table, and matching records from the left table
✅ FULL OUTER JOIN - Returns all records from both tables

Basic OUTER JOIN Syntax:

SELECT column_name(s)
FROM table1
LEFT/RIGHT/FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table, and the matching records from the right table. The result is NULL from the right side, if there is no match.

LEFT JOIN Syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2 
ON table1.column_name = table2.column_name;

 

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table, and the matching records from the left table. The result is NULL from the left side, if there is no match.

RIGHT JOIN Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 
ON table1.column_name = table2.column_name;

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in either the left or right table. It combines the results of both LEFT and RIGHT JOINs.

FULL OUTER JOIN Syntax:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 
ON table1.column_name = table2.column_name;

 

Working with NULL Values

OUTER JOINs often produce NULL values in the results. You can use these techniques to work with NULLs:

✅ IS NULL - Find records with missing data
✅ IS NOT NULL - Find records with existing data
✅ WHERE conditions - Filter based on NULL values

When to Use OUTER JOINs

OUTER JOINs are useful when you need to:

🔍 See all records from one table, regardless of matches
📊 Find missing data or incomplete relationships
🔗 Create complete reports that include all entities
📈 Analyze data gaps in your database

Did you know?

You might see OUTER JOINs written as LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN. The OUTER keyword is optional - LEFT JOIN, RIGHT JOIN, and FULL JOIN mean exactly the same thing.

Most databases support LEFT JOIN and RIGHT JOIN, but FULL OUTER JOIN support varies between database systems.

---

✅ Key Takeaways

• OUTER JOINs include records even when there's no match

• LEFT JOIN keeps all records from the left table
• RIGHT JOIN keeps all records from the right table
• FULL OUTER JOIN keeps all records from both tables
• NULL values appear when there's no matching data
• Use WHERE conditions to filter NULL or non-NULL results

---

📝 Exercise Overview

In this lesson's exercises, you'll work with a Companies and Orders dataset. Some companies in our business directory haven't placed any orders yet, and some orders might have incomplete company information.

This real-world scenario will help you understand when OUTER JOINs are more useful than INNER JOINs for complete data analysis.

 

 

 

 

 

 

---

Ready to practice SQL? Try our coding challenges →