Lesson 6: OUTER JOINs - Finding All Records

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. Think of them as more inclusive joins that don't leave any records behind.

🔍 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.

The 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 Explained

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 when there is no match.

LEFT JOIN Syntax:

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

Example with sample data:

Companies Table (Left):

company_id | company_name    | city
-----------|-----------------|----------
1          | Tech Solutions  | New York
2          | Global Corp     | London
3          | StartUp Inc     | Austin

Orders Table (Right):

order_id | company_id | order_amount | order_date
---------|------------|--------------|------------
101      | 1          | 5000         | 2024-01-15
102      | 1          | 3000         | 2024-02-20
103      | 2          | 7500         | 2024-03-10

LEFT JOIN Result:

SELECT companies.company_name, orders.order_amount
FROM companies
LEFT JOIN orders ON companies.company_id = orders.company_id;

company_name    | order_amount
----------------|-------------
Tech Solutions  | 5000
Tech Solutions  | 3000
Global Corp     | 7500
StartUp Inc     | NULL

💡 Key Point: Notice how "StartUp Inc" appears in the result even though it has no orders. This is the power of LEFT JOIN - it keeps all records from the left table.

➡️ RIGHT JOIN Explained

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 when there is no match.

RIGHT JOIN Syntax:

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

Example:

SELECT companies.company_name, orders.order_amount
FROM companies
RIGHT JOIN orders ON companies.company_id = orders.company_id;

This ensures all orders are included, even if company information is missing.

↔️ FULL OUTER JOIN Explained

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;

Example:

SELECT companies.company_name, orders.order_amount
FROM companies
FULL OUTER JOIN orders ON companies.company_id = orders.company_id;

This returns all companies (even without orders) and all orders (even without company info).

🔧 Working with NULL Values

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

Finding records with missing data:

SELECT companies.company_name, orders.order_amount
FROM companies
LEFT JOIN orders ON companies.company_id = orders.company_id
WHERE orders.order_amount IS NULL;

Finding records with existing data:

SELECT companies.company_name, orders.order_amount
FROM companies
LEFT JOIN orders ON companies.company_id = orders.company_id
WHERE orders.order_amount IS NOT NULL;

Filtering based on NULL values:

-- Companies that have never placed an order
SELECT company_name
FROM companies
LEFT JOIN orders ON companies.company_id = orders.company_id
WHERE orders.company_id IS NULL;

🎯 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
  • Identify customers without orders or products without sales
  • Generate comprehensive dashboards that show full business scope

Common business scenarios:

-- Find all customers, including those who haven't made purchases
SELECT customers.name, orders.total_amount
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

-- Find all products, including those that haven't been sold
SELECT products.product_name, sales.quantity_sold
FROM products
LEFT JOIN sales ON products.product_id = sales.product_id;

💡 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 in the other table
  • LEFT JOIN keeps all records from the left table, with NULLs for missing right table data
  • RIGHT JOIN keeps all records from the right table, with NULLs for missing left table data
  • FULL OUTER JOIN keeps all records from both tables, with NULLs where data is missing
  • NULL values appear when there's no matching data in the joined table
  • Use IS NULL and IS NOT NULL to filter results based on missing data
  • OUTER JOINs are essential for complete data analysis and finding gaps in your data
  • Choose the right JOIN type based on which table's records you want to preserve
  • LEFT JOIN is most commonly used in real-world applications

📝 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. You'll practice:

  • Using LEFT JOIN to find all companies, including those without orders
  • Working with NULL values to identify missing relationships
  • Combining WHERE clauses with OUTER JOINs to filter results
  • Understanding the difference between INNER and OUTER JOIN results

Take your time with these exercises - understanding OUTER JOINs is crucial for real-world data analysis where incomplete data is common!  

Previous Lesson
Next Lesson

Ready to practice SQL? Try our coding challenges →