Module 3.2

SQL Joins

Master the art of combining data from multiple tables! Learn INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN to unlock the full power of relational databases.

40 min read
Intermediate
Hands-on Examples
What You'll Learn
  • Understanding table relationships
  • INNER JOIN for matching records
  • LEFT and RIGHT JOINs
  • FULL OUTER JOIN
  • Joining multiple tables
Contents
01

Introduction to Joins

Real-world data is rarely stored in a single table. In relational databases, data is split across multiple tables to avoid redundancy. Joins are how we recombine that data to answer meaningful questions.

Why Do We Need Joins?

Imagine you're analyzing e-commerce data. You have:

  • Customers table - customer names, emails, locations
  • Orders table - order IDs, dates, amounts, customer IDs
  • Products table - product names, prices, categories

To answer questions like "What products did customer John Smith buy?" or "Which customers from California made orders over $500?", you need to combine data from multiple tables. That's where joins come in!

Key Concept: Joins use a common column (usually a foreign key) to match rows between tables. For example, the customer_id in the orders table references the id in the customers table.

Sample Database for This Lesson

We'll use two related tables throughout this lesson:

Table: customers 
+----+--------------+----------------------+
| id | name         | city                 |
+----+--------------+----------------------+
| 1  | Alice        | New York             |
| 2  | Bob          | Los Angeles          |
| 3  | Charlie      | Chicago              |
| 4  | Diana        | Houston              |
| 5  | Eve          | Phoenix              |
+----+--------------+----------------------+
Table: orders 
+----+-------------+--------+
| id | customer_id | amount |
+----+-------------+--------+
| 1  | 1           | 250    |
| 2  | 2           | 400    |
| 3  | 1           | 150    |
| 4  | 3           | 600    |
| 5  | 6           | 300    |
+----+-------------+--------+
Notice:
  • The customer_id column in orders references id in customers
  • Alice (id=1) has 2 orders, Bob (id=2) and Charlie (id=3) have 1 each
  • Diana (id=4) and Eve (id=5) have NO orders
  • Order 5 has customer_id=6, but there's NO customer with id=6 (orphaned record)

Types of Joins

SQL provides several types of joins, each serving a different purpose:

INNER JOIN

Returns only rows where there's a match in both tables. Most common join type.

LEFT JOIN

Returns all rows from the left table, plus matching rows from the right (NULLs if no match).

RIGHT JOIN

Returns all rows from the right table, plus matching rows from the left (NULLs if no match).

FULL OUTER JOIN

Returns all rows from both tables, with NULLs where matches don't exist. Combines LEFT and RIGHT.

Foreign Keys and Relationships

Joins work by matching values in related columns:

  • Primary Key - Unique identifier for each row (e.g., customers.id)
  • Foreign Key - Column that references a primary key in another table (e.g., orders.customer_id)
Basic JOIN Syntax 
SELECT columns
FROM table1
JOIN table2
  ON table1.column = table2.column;

The ON clause specifies how to match rows between tables.

Practice Questions: Understanding Joins

Question: Looking at our sample tables:

  1. Which column in the orders table is the foreign key?
  2. Which column does it reference in the customers table?
  3. How many customers have made orders?
Show Solution

Answers:

  1. Foreign Key: customer_id in the orders table
  2. References: id column in the customers table
  3. Count: 3 customers have orders (Alice, Bob, Charlie)

Explanation:

  • customer_id values (1, 2, 3) match customer ids
  • Diana (id=4) and Eve (id=5) have no orders
  • Order 5 has customer_id=6 which doesn't exist (data quality issue)

Task: Without running the query, predict:

  • If we INNER JOIN customers and orders, how many result rows will we get?
  • Will Diana appear in the results?
  • Will Order 5 (customer_id=6) appear?
Show Solution

Predictions:

  • Result rows: 4 rows (only orders with matching customers)
  • Diana: NO - she has no orders, so no match
  • Order 5: NO - customer_id=6 doesn't exist in customers table

Reasoning:

INNER JOIN only returns rows where there's a match in BOTH tables. Since Diana has no orders and Order 5 has no matching customer, they're excluded from results.

Question: For each scenario, which join type would you use?

  1. Find all customers who have placed orders
  2. List all customers, including those who haven't ordered yet
  3. Find orders where the customer information is missing
Show Solution

Solutions:

  1. INNER JOIN - Only customers with orders
  2. LEFT JOIN - All customers (left table), with or without orders
  3. LEFT JOIN with WHERE customer_id IS NULL - Finds orphaned orders

Explanation:

The join type depends on whether you want ALL records from one table or only MATCHING records from both. We'll explore each join type in detail in the following sections!

02

INNER JOIN

INNER JOIN is the most commonly used join type. It returns only the rows where there is a match in both tables based on the join condition.

Basic Syntax

INNER JOIN Syntax 
SELECT table1.column1, table2.column2, ...
FROM table1
INNER JOIN table2
  ON table1.common_column = table2.common_column;
What It Does:

INNER JOIN compares each row from the first table with each row from the second table. When the join condition is true (values match), it combines those rows into a single result row. Rows without matches are excluded.

Example 1: Basic INNER JOIN

Let's join our customers and orders tables to see who ordered what:

Query 
SELECT customers.name, customers.city, orders.id AS order_id, orders.amount
FROM customers
INNER JOIN orders
  ON customers.id = orders.customer_id;
Result (4 rows) 
+---------+-------------+----------+--------+
| name    | city        | order_id | amount |
+---------+-------------+----------+--------+
| Alice   | New York    | 1        | 250    |
| Bob     | Los Angeles | 2        | 400    |
| Alice   | New York    | 3        | 150    |
| Charlie | Chicago     | 4        | 600    |
+---------+-------------+----------+--------+
Notice: Only 4 rows returned (not all 5 customers or 5 orders) because:
  • Diana and Eve have no orders, so they're excluded
  • Order 5 (customer_id=6) has no matching customer, so it's excluded
  • Alice appears twice because she has 2 orders

Example 2: INNER JOIN with WHERE

You can add conditions to filter the joined data:

Find orders over $300 
SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders
  ON customers.id = orders.customer_id
WHERE orders.amount > 300;
Result (2 rows) 
+---------+--------+
| name    | amount |
+---------+--------+
| Bob     | 400    |
| Charlie | 600    |
+---------+--------+

Example 3: Selecting Specific Columns

Use table prefixes to avoid ambiguity when columns have the same name:

Using Aliases 
SELECT c.name AS customer_name, 
       c.city,
       o.id AS order_number,
       o.amount AS order_total
FROM customers AS c
INNER JOIN orders AS o
  ON c.id = o.customer_id
ORDER BY o.amount DESC;
Result (ordered by amount) 
+---------------+-------------+--------------+-------------+
| customer_name | city        | order_number | order_total |
+---------------+-------------+--------------+-------------+
| Charlie       | Chicago     | 4            | 600         |
| Bob           | Los Angeles | 2            | 400         |
| Alice         | New York    | 1            | 250         |
| Alice         | New York    | 3            | 150         |
+---------------+-------------+--------------+-------------+
Pro Tip: Using table aliases (c, o) makes your queries shorter and more readable. This becomes essential when joining many tables!

INNER JOIN vs JOIN

Good news - JOIN and INNER JOIN are the same thing!

Option 1: Explicit 
SELECT *
FROM customers
INNER JOIN orders
  ON customers.id = orders.customer_id;
Option 2: Implicit 
SELECT *
FROM customers
JOIN orders
  ON customers.id = orders.customer_id;

Both queries produce identical results. Most developers use INNER JOIN for clarity, but JOIN alone defaults to INNER JOIN.

Practice Questions: INNER JOIN

Task: Write a query to find the names of customers who have placed orders. Show only customer names (no duplicates).

Hint: Use DISTINCT to remove duplicate names.
Show Solution
Solution 
SELECT DISTINCT customers.name
FROM customers
INNER JOIN orders
  ON customers.id = orders.customer_id;
Result 
+---------+
| name    |
+---------+
| Alice   |
| Bob     |
| Charlie |
+---------+

DISTINCT removes Alice's duplicate (she has 2 orders). Only customers with orders appear.

Task: Calculate the total amount spent by each customer. Show customer name, city, and total amount. Order by total amount descending.

Hint: Use SUM() and GROUP BY with the customer name and city.
Show Solution
Solution 
SELECT customers.name,
       customers.city,
       SUM(orders.amount) AS total_spent
FROM customers
INNER JOIN orders
  ON customers.id = orders.customer_id
GROUP BY customers.name, customers.city
ORDER BY total_spent DESC;
Result 
+---------+-------------+-------------+
| name    | city        | total_spent |
+---------+-------------+-------------+
| Charlie | Chicago     | 600         |
| Bob     | Los Angeles | 400         |
| Alice   | New York    | 400         |
+---------+-------------+-------------+

Alice's two orders (250 + 150 = 400) are summed together. Diana and Eve don't appear because they have no orders to join.

Task: Find customers from New York or Chicago who have orders over $200. Show customer name, city, order ID, and amount.

Hint: Use WHERE with multiple conditions (IN and comparison operators).
Show Solution
Solution 
SELECT c.name, 
       c.city, 
       o.id AS order_id, 
       o.amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.id = o.customer_id
WHERE c.city IN ('New York', 'Chicago')
  AND o.amount > 200
ORDER BY o.amount DESC;
Result 
+---------+----------+----------+--------+
| name    | city     | order_id | amount |
+---------+----------+----------+--------+
| Charlie | Chicago  | 4        | 600    |
| Alice   | New York | 1        | 250    |
+---------+----------+----------+--------+

Alice's $150 order is excluded (≤ $200). Bob is excluded (not from NY or Chicago). The WHERE clause filters after the join completes.

03

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table, plus matching rows from the right table. When there's no match, the result contains NULL values for the right table's columns.

Basic Syntax

LEFT JOIN Syntax 
SELECT table1.column1, table2.column2, ...
FROM table1
LEFT JOIN table2
  ON table1.common_column = table2.common_column;
Key Difference from INNER JOIN: LEFT JOIN keeps ALL rows from the left table, even if they don't have matches in the right table. Missing data is represented as NULL.

Example 1: Basic LEFT JOIN

Let's get ALL customers, including those who haven't placed orders:

Query 
SELECT customers.name, 
       customers.city, 
       orders.id AS order_id, 
       orders.amount
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
ORDER BY customers.id;
Result (6 rows) 
+---------+-------------+----------+--------+
| name    | city        | order_id | amount |
+---------+-------------+----------+--------+
| Alice   | New York    | 1        | 250    |
| Alice   | New York    | 3        | 150    |
| Bob     | Los Angeles | 2        | 400    |
| Charlie | Chicago     | 4        | 600    |
| Diana   | Houston     | NULL     | NULL   |
| Eve     | Phoenix     | NULL     | NULL   |
+---------+-------------+----------+--------+
Notice the difference:
  • All 5 customers appear (including Diana and Eve)
  • Diana and Eve have NULL for order_id and amount (no matching orders)
  • Alice still appears twice (she has 2 orders)
  • Order 5 (customer_id=6) is NOT included - it's from the right table without a left match

Example 2: Finding Non-Matching Rows

LEFT JOIN is perfect for finding records that DON'T have matches:

Find customers with NO orders 
SELECT customers.name, customers.city
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
WHERE orders.id IS NULL;
Result (2 rows) 
+-------+---------+
| name  | city    |
+-------+---------+
| Diana | Houston |
| Eve   | Phoenix |
+-------+---------+
Pro Technique:

Using WHERE orders.id IS NULL filters for rows where the join found no match. This is a common pattern for finding "orphaned" or "missing" data.

Example 3: LEFT JOIN with Aggregation

Calculate total spending per customer, including those who haven't ordered:

Query 
SELECT customers.name,
       customers.city,
       COUNT(orders.id) AS order_count,
       COALESCE(SUM(orders.amount), 0) AS total_spent
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
GROUP BY customers.name, customers.city
ORDER BY total_spent DESC;
Result (5 rows) 
+---------+-------------+-------------+-------------+
| name    | city        | order_count | total_spent |
+---------+-------------+-------------+-------------+
| Charlie | Chicago     | 1           | 600         |
| Alice   | New York    | 2           | 400         |
| Bob     | Los Angeles | 1           | 400         |
| Diana   | Houston     | 0           | 0           |
| Eve     | Phoenix     | 0           | 0           |
+---------+-------------+-------------+-------------+
COALESCE() Function: Replaces NULL values with 0. Without it, Diana and Eve would show NULL for total_spent. COALESCE returns the first non-NULL value.

When to Use LEFT JOIN

Use LEFT JOIN when:
  • You want all records from the main table
  • Finding records without matches (orphans)
  • Counting related records (including zero counts)
  • Optional relationships (customers may or may not have orders)
Don't use LEFT JOIN when:
  • You only need records with matches (use INNER JOIN)
  • Performance is critical and you don't need non-matches
  • The relationship is required (every order must have a customer)

Practice Questions: LEFT JOIN

Task: Write a query showing all customers and their order amounts. If a customer has no orders, show 0 instead of NULL.

Hint: Use COALESCE() or IFNULL() to replace NULL with 0.
Show Solution
Solution 
SELECT customers.name,
       COALESCE(orders.amount, 0) AS amount
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
ORDER BY customers.name;

COALESCE returns the first non-NULL value. For Diana and Eve, it returns 0. Alternative: IFNULL(orders.amount, 0) works the same in MySQL.

Task: Identify customers who have NOT placed any orders. Show customer name and city only.

Hint: LEFT JOIN + WHERE column IS NULL finds non-matches.
Show Solution
Solution 
SELECT customers.name, customers.city
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
WHERE orders.id IS NULL;
Expected Result 
+-------+---------+
| name  | city    |
+-------+---------+
| Diana | Houston |
| Eve   | Phoenix |
+-------+---------+

This pattern is extremely useful for data quality checks, finding inactive users, or identifying missing relationships.

Task: Create a complete customer activity report showing: customer name, city, number of orders, total spent, and average order value. Include customers with zero orders. Order by total spent descending.

Hint: Use LEFT JOIN, GROUP BY, COUNT(), SUM(), AVG(), and COALESCE().
Show Solution
Solution 
SELECT customers.name,
       customers.city,
       COUNT(orders.id) AS order_count,
       COALESCE(SUM(orders.amount), 0) AS total_spent,
       COALESCE(AVG(orders.amount), 0) AS avg_order_value
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name, customers.city
ORDER BY total_spent DESC;
Expected Result 
+---------+-------------+-------------+-------------+-----------------+
| name    | city        | order_count | total_spent | avg_order_value |
+---------+-------------+-------------+-------------+-----------------+
| Charlie | Chicago     | 1           | 600         | 600.00          |
| Alice   | New York    | 2           | 400         | 200.00          |
| Bob     | Los Angeles | 1           | 400         | 400.00          |
| Diana   | Houston     | 0           | 0           | 0.00            |
| Eve     | Phoenix     | 0           | 0           | 0.00            |
+---------+-------------+-------------+-------------+-----------------+

This type of report is common in business analytics. Note that COUNT(orders.id) correctly returns 0 for customers without orders (counting NULLs gives 0).

04

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN is the mirror image of LEFT JOIN. It returns all rows from the right table, plus matching rows from the left table. When there's no match, the result contains NULL values for the left table's columns.

Basic Syntax

RIGHT JOIN Syntax 
SELECT table1.column1, table2.column2, ...
FROM table1
RIGHT JOIN table2
  ON table1.common_column = table2.common_column;
Important Note: RIGHT JOIN is less commonly used than LEFT JOIN. Most developers prefer to rewrite queries using LEFT JOIN by switching table order. They produce equivalent results!

Example 1: Basic RIGHT JOIN

Let's get ALL orders, including those without matching customers:

Query 
SELECT customers.name, 
       customers.city, 
       orders.id AS order_id, 
       orders.amount
FROM customers
RIGHT JOIN orders
  ON customers.id = orders.customer_id
ORDER BY orders.id;
Result (5 rows) 
+---------+-------------+----------+--------+
| name    | city        | order_id | amount |
+---------+-------------+----------+--------+
| Alice   | New York    | 1        | 250    |
| Bob     | Los Angeles | 2        | 400    |
| Alice   | New York    | 3        | 150    |
| Charlie | Chicago     | 4        | 600    |
| NULL    | NULL        | 5        | 300    |
+---------+-------------+----------+--------+
Notice:
  • All 5 orders appear (including order 5 with no matching customer)
  • Order 5 has NULL for name and city (customer_id=6 doesn't exist)
  • Diana and Eve DON'T appear (they're from the left table without matches in orders)

Example 2: RIGHT JOIN = Reversed LEFT JOIN

These two queries produce identical results:

Using RIGHT JOIN 
SELECT customers.name, orders.amount
FROM customers
RIGHT JOIN orders
  ON customers.id = orders.customer_id;
Using LEFT JOIN (Preferred) 
SELECT customers.name, orders.amount
FROM orders
LEFT JOIN customers
  ON orders.customer_id = customers.id;
Best Practice:

Most SQL developers prefer LEFT JOIN because it's more intuitive to read from left to right. If you need a RIGHT JOIN, consider rewriting it as a LEFT JOIN by swapping the table order.

Example 3: Finding Orphaned Orders

Use RIGHT JOIN to find orders without valid customers (data quality check):

Find orders with invalid customer_id 
SELECT orders.id AS order_id,
       orders.customer_id,
       orders.amount
FROM customers
RIGHT JOIN orders
  ON customers.id = orders.customer_id
WHERE customers.id IS NULL;
Result (1 row) 
+----------+-------------+--------+
| order_id | customer_id | amount |
+----------+-------------+--------+
| 5        | 6           | 300    |
+----------+-------------+--------+
Data Quality Issue: Order 5 references customer_id=6, but no such customer exists. This is called an "orphaned record" and should be investigated or cleaned up.

LEFT vs RIGHT: Quick Comparison

Aspect LEFT JOIN RIGHT JOIN
Keeps all rows from Left table (first table) Right table (second table)
NULL appears in Right table columns (when no match) Left table columns (when no match)
Usage frequency Very common (80%+ of joins) Rare (most convert to LEFT JOIN)
Typical use case "Show all customers, including those without orders" "Show all orders, including those without customers"
Can be converted? Yes, to RIGHT JOIN by swapping tables Yes, to LEFT JOIN by swapping tables

Practice Questions: RIGHT JOIN

Task: Show all orders with customer names. If an order has no matching customer, show "Unknown Customer" instead of NULL.

Hint: Use COALESCE() or IFNULL() to replace NULL names.
Show Solution
Solution 
SELECT orders.id AS order_id,
       COALESCE(customers.name, 'Unknown Customer') AS customer_name,
       orders.amount
FROM customers
RIGHT JOIN orders
  ON customers.id = orders.customer_id
ORDER BY orders.id;

Order 5 will show "Unknown Customer" since customer_id=6 doesn't exist.

Task: Rewrite this RIGHT JOIN query as a LEFT JOIN:

SELECT customers.name, orders.amount
FROM customers
RIGHT JOIN orders
  ON customers.id = orders.customer_id;
Show Solution
Solution 
SELECT customers.name, orders.amount
FROM orders
LEFT JOIN customers
  ON orders.customer_id = customers.id;

Simply swap the table positions: orders becomes the left table, customers becomes the right. The ON clause columns also switch to match the new table order. Results are identical!

Task: Create a data integrity report showing all orders with their status. If customer exists, show "Valid", otherwise show "Invalid - Missing Customer". Include order ID, customer_id, amount, and status. Order by status.

Hint: Use CASE WHEN with IS NULL check to create the status column.
Show Solution
Solution 
SELECT orders.id AS order_id,
       orders.customer_id,
       orders.amount,
       CASE 
         WHEN customers.id IS NOT NULL THEN 'Valid'
         ELSE 'Invalid - Missing Customer'
       END AS status
FROM customers
RIGHT JOIN orders
  ON customers.id = orders.customer_id
ORDER BY status DESC, orders.id;
Expected Result 
+----------+-------------+--------+---------------------------+
| order_id | customer_id | amount | status                    |
+----------+-------------+--------+---------------------------+
| 1        | 1           | 250    | Valid                     |
| 2        | 2           | 400    | Valid                     |
| 3        | 1           | 150    | Valid                     |
| 4        | 3           | 600    | Valid                     |
| 5        | 6           | 300    | Invalid - Missing Customer|
+----------+-------------+--------+---------------------------+

This type of data quality report helps identify referential integrity issues. In a real database with foreign key constraints, order 5 would never be allowed.

05

FULL OUTER JOIN

FULL OUTER JOIN combines LEFT JOIN and RIGHT JOIN. It returns all rows from both tables, with NULL values where matches don't exist. Think of it as "give me everything."

Basic Syntax

FULL OUTER JOIN Syntax 
SELECT table1.column1, table2.column2, ...
FROM table1
FULL OUTER JOIN table2
  ON table1.common_column = table2.common_column;
Database Compatibility: FULL OUTER JOIN is NOT supported in MySQL (as of version 8.0). It works in PostgreSQL, SQL Server, and Oracle. In MySQL, you can emulate it using UNION of LEFT and RIGHT joins.

What Does It Return?

Matching Rows

Rows where join condition is true in both tables

Left-Only Rows

Rows from left table with no right match (right columns = NULL)

Right-Only Rows

Rows from right table with no left match (left columns = NULL)

Example 1: FULL OUTER JOIN (PostgreSQL/SQL Server)

Get ALL customers and ALL orders, showing matches where they exist:

Query (PostgreSQL/SQL Server) 
SELECT customers.name, 
       customers.city, 
       orders.id AS order_id, 
       orders.amount
FROM customers
FULL OUTER JOIN orders
  ON customers.id = orders.customer_id
ORDER BY customers.id, orders.id;
Result (7 rows - everyone and everything!) 
+---------+-------------+----------+--------+
| name    | city        | order_id | amount |
+---------+-------------+----------+--------+
| Alice   | New York    | 1        | 250    |
| Alice   | New York    | 3        | 150    |
| Bob     | Los Angeles | 2        | 400    |
| Charlie | Chicago     | 4        | 600    |
| Diana   | Houston     | NULL     | NULL   |
| Eve     | Phoenix     | NULL     | NULL   |
| NULL    | NULL        | 5        | 300    |
+---------+-------------+----------+--------+
Complete Picture:
  • Customers with orders: Alice (2 orders), Bob, Charlie
  • Customers without orders: Diana, Eve (customer columns filled, order columns NULL)
  • Orders without customers: Order 5 (order columns filled, customer columns NULL)

Example 2: Emulating FULL OUTER JOIN in MySQL

Since MySQL doesn't support FULL OUTER JOIN, use UNION to combine LEFT and RIGHT joins:

MySQL Workaround 
SELECT customers.name, customers.city, orders.id AS order_id, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id

UNION

SELECT customers.name, customers.city, orders.id AS order_id, orders.amount
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id
WHERE customers.id IS NULL;
How It Works:
  • First query (LEFT JOIN): Gets all customers + matching orders
  • UNION: Combines results, removing duplicates
  • Second query (RIGHT JOIN + WHERE): Gets orders without customers only
  • Result: Same as FULL OUTER JOIN!

Example 3: Finding All Mismatches

Find records that DON'T have matches (customers without orders + orders without customers):

Find All Orphaned Records 
SELECT customers.name, 
       customers.city,
       orders.id AS order_id,
       orders.customer_id,
       CASE
         WHEN customers.id IS NULL THEN 'Order without customer'
         WHEN orders.id IS NULL THEN 'Customer without orders'
       END AS issue_type
FROM customers
FULL OUTER JOIN orders
  ON customers.id = orders.customer_id
WHERE customers.id IS NULL OR orders.id IS NULL;
Result (3 rows - all the problems!) 
+-------+---------+----------+-------------+------------------------+
| name  | city    | order_id | customer_id | issue_type             |
+-------+---------+----------+-------------+------------------------+
| Diana | Houston | NULL     | NULL        | Customer without orders|
| Eve   | Phoenix | NULL     | NULL        | Customer without orders|
| NULL  | NULL    | 5        | 6           | Order without customer |
+-------+---------+----------+-------------+------------------------+

When to Use FULL OUTER JOIN

Good Use Cases:
  • Data reconciliation between systems
  • Finding all mismatches/orphans at once
  • Complete audit reports
  • Comparing two datasets for differences
Caution:
  • Can return very large result sets
  • Not available in MySQL (requires workaround)
  • Usually LEFT or INNER JOIN is sufficient
  • Performance impact on large tables

Practice Questions: FULL OUTER JOIN

Task: Write a MySQL-compatible query that shows all customers and all orders, including those without matches. Show customer name and order amount only.

Hint: Use UNION to combine LEFT JOIN (all customers) with RIGHT JOIN filtered for unmatched orders.
Show Solution
Solution 
SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id

UNION

SELECT customers.name, orders.amount
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id
WHERE customers.id IS NULL;

UNION automatically removes duplicates, giving us the complete set. The WHERE clause in the second query ensures we only add orders without customers.

Task: Create a comprehensive report showing customer name, order count, and total spent for EVERYONE (customers with and without orders). Also flag any orphaned orders. Use COALESCE for NULL handling.

Hint: This requires a more complex FULL OUTER JOIN with aggregation and CASE statements.
Show Solution
Solution (PostgreSQL/SQL Server) 
SELECT 
  COALESCE(customers.name, 'ORPHANED ORDER') AS customer_name,
  COUNT(orders.id) AS order_count,
  COALESCE(SUM(orders.amount), 0) AS total_spent,
  CASE 
    WHEN customers.id IS NULL THEN 'Data Issue'
    WHEN COUNT(orders.id) = 0 THEN 'No Activity'
    ELSE 'Active'
  END AS status
FROM customers
FULL OUTER JOIN orders
  ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name
ORDER BY total_spent DESC;

This report gives a complete picture: active customers, inactive customers, and data quality issues (orphaned orders) - all in one view!

Question: Based on our sample data, answer these:

  1. How many total rows does FULL OUTER JOIN return? (customers: 5, orders: 5)
  2. How many rows have NULLs in customer columns?
  3. How many rows have NULLs in order columns?
Show Solution

Answers:

  1. 7 rows total - Alice appears twice (2 orders), plus Bob, Charlie, Diana, Eve, and orphaned Order 5
  2. 1 row with NULL customers - Order 5 (customer_id=6 doesn't exist)
  3. 2 rows with NULL orders - Diana and Eve (no orders placed)

Formula:

FULL OUTER JOIN rows = Matching rows + Left-only rows + Right-only rows
= 4 (matching) + 2 (Diana, Eve) + 1 (Order 5) = 7 rows

06

Joining Multiple Tables

Real-world databases often require joining three or more tables to answer complex questions. You can chain multiple JOIN clauses together to connect as many tables as needed.

Basic Syntax

Multiple Joins Syntax 
SELECT table1.col, table2.col, table3.col
FROM table1
JOIN table2 ON table1.id = table2.table1_id
JOIN table3 ON table2.id = table3.table2_id;
Join Order: Joins are evaluated from top to bottom (left to right). Each JOIN adds another table to the result set. You can mix INNER, LEFT, RIGHT, and FULL joins in the same query!

Extended Sample Database

Let's add a products table to our example:

customers 
+----+---------+
| id | name    |
+----+---------+
| 1  | Alice   |
| 2  | Bob     |
| 3  | Charlie |
+----+---------+
orders 
+----+------+------------+
| id | cust | product_id |
+----+------+------------+
| 1  | 1    | 101        |
| 2  | 2    | 102        |
| 3  | 1    | 103        |
| 4  | 3    | 101        |
+----+------+------------+
products 
+-----+---------+-------+
| id  | name    | price |
+-----+---------+-------+
| 101 | Laptop  | 1000  |
| 102 | Mouse   | 25    |
| 103 | Keyboard| 75    |
+-----+---------+-------+

Example 1: Three-Table Join

Show customer names with the products they ordered:

Query 
SELECT customers.name AS customer,
       products.name AS product,
       products.price
FROM customers
INNER JOIN orders 
  ON customers.id = orders.cust
INNER JOIN products 
  ON orders.product_id = products.id
ORDER BY customers.name, products.name;
Result (4 rows) 
+----------+----------+-------+
| customer | product  | price |
+----------+----------+-------+
| Alice    | Keyboard | 75    |
| Alice    | Laptop   | 1000  |
| Bob      | Mouse    | 25    |
| Charlie  | Laptop   | 1000  |
+----------+----------+-------+
Join Flow:
  1. Start with customers table
  2. Join to orders (connecting customers.id = orders.cust)
  3. Join to products (connecting orders.product_id = products.id)
  4. Result includes columns from all three tables

Example 2: Aggregating Across Multiple Tables

Calculate total spending per customer across all products:

Query 
SELECT customers.name,
       COUNT(orders.id) AS items_purchased,
       SUM(products.price) AS total_spent
FROM customers
INNER JOIN orders ON customers.id = orders.cust
INNER JOIN products ON orders.product_id = products.id
GROUP BY customers.id, customers.name
ORDER BY total_spent DESC;
Result (3 rows) 
+---------+------------------+-------------+
| name    | items_purchased  | total_spent |
+---------+------------------+-------------+
| Alice   | 2                | 1075        |
| Charlie | 1                | 1000        |
| Bob     | 1                | 25          |
+---------+------------------+-------------+

Example 3: Mixing Join Types

Show all customers, including those without orders, but only for valid products:

Query with LEFT and INNER JOINs 
SELECT customers.name,
       COALESCE(products.name, 'No orders') AS product,
       COALESCE(products.price, 0) AS price
FROM customers
LEFT JOIN orders 
  ON customers.id = orders.cust
LEFT JOIN products 
  ON orders.product_id = products.id
ORDER BY customers.name;
Important: When mixing joins, order matters! If you use INNER JOIN after LEFT JOIN, you may lose the "all rows" behavior from LEFT JOIN. Use LEFT JOIN for all subsequent joins if you want to preserve unmatched rows.

Complex Example: 4+ Tables

Real e-commerce systems might join many tables:

Complex Multi-Table Query 
SELECT 
  customers.name AS customer,
  orders.id AS order_number,
  products.name AS product,
  categories.name AS category,
  shipping.status AS delivery_status
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id
INNER JOIN categories ON products.category_id = categories.id
LEFT JOIN shipping ON orders.id = shipping.order_id
WHERE orders.order_date >= '2024-01-01'
ORDER BY customers.name, orders.id;
Join Chain:

customers → orders → order_items → products → categories → shipping
This creates a data pipeline connecting 6 tables to answer complex business questions!

Performance Tips

Best Practices:
  • Join on indexed columns (primary/foreign keys)
  • Filter early with WHERE conditions
  • Select only needed columns, not SELECT *
  • Use table aliases for readability
  • Test queries on small datasets first
Avoid:
  • Joining without indexes (very slow!)
  • Cartesian products (missing ON clause)
  • Too many joins (>5-6 tables gets complex)
  • Joining on non-unique columns unnecessarily
  • Using functions in ON clause (defeats indexes)

Practice Questions: Multiple Joins

Task: Using our three-table database (customers, orders, products), write a query to find which products Alice has purchased. Show product name and price only.

Hint: Join all three tables, then filter WHERE customers.name = 'Alice'.
Show Solution
Solution 
SELECT products.name AS product, products.price
FROM customers
INNER JOIN orders ON customers.id = orders.cust
INNER JOIN products ON orders.product_id = products.id
WHERE customers.name = 'Alice';
Expected Result 
+----------+-------+
| product  | price |
+----------+-------+
| Laptop   | 1000  |
| Keyboard | 75    |
+----------+-------+

Alice purchased 2 items: a Laptop ($1000) and a Keyboard ($75).

Task: Create a product popularity report showing: product name, number of times ordered, total revenue, and list of customer names who bought it (comma-separated). Order by revenue descending.

Hint: Use GROUP BY products, COUNT(), SUM(), and GROUP_CONCAT() or STRING_AGG().
Show Solution
Solution (MySQL) 
SELECT 
  products.name AS product,
  COUNT(orders.id) AS times_ordered,
  SUM(products.price) AS total_revenue,
  GROUP_CONCAT(customers.name ORDER BY customers.name) AS customers
FROM products
INNER JOIN orders ON products.id = orders.product_id
INNER JOIN customers ON orders.cust = customers.id
GROUP BY products.id, products.name, products.price
ORDER BY total_revenue DESC;
Expected Result 
+----------+---------------+---------------+----------------+
| product  | times_ordered | total_revenue | customers      |
+----------+---------------+---------------+----------------+
| Laptop   | 2             | 2000          | Alice,Charlie  |
| Keyboard | 1             | 75            | Alice          |
| Mouse    | 1             | 25            | Bob            |
+----------+---------------+---------------+----------------+

Laptop is the most popular (2 sales, $2000 revenue). Note: Use STRING_AGG() in PostgreSQL instead of GROUP_CONCAT().

Task: Find customers who have purchased products costing $100 or more. Show customer name, product name, and price. Include customers with multiple qualifying purchases.

Hint: Join all three tables and filter WHERE products.price >= 100.
Show Solution
Solution 
SELECT 
  customers.name AS customer,
  products.name AS product,
  products.price
FROM customers
INNER JOIN orders ON customers.id = orders.cust
INNER JOIN products ON orders.product_id = products.id
WHERE products.price >= 100
ORDER BY products.price DESC, customers.name;
Expected Result 
+----------+--------+-------+
| customer | product| price |
+----------+--------+-------+
| Alice    | Laptop | 1000  |
| Charlie  | Laptop | 1000  |
+----------+--------+-------+

Only Alice and Charlie bought expensive products ($100+). Bob's Mouse ($25) and Alice's Keyboard ($75) are excluded.

Key Takeaways

Joins Connect Tables

SQL joins combine rows from multiple tables based on related columns (foreign keys)

INNER JOIN

Returns only rows where there's a match in both tables - the most common join type

LEFT JOIN

Returns all rows from the left table, with NULLs where no match exists in the right table

RIGHT JOIN

Returns all rows from the right table, with NULLs where no match exists in the left table

FULL OUTER JOIN

Returns all rows from both tables, with NULLs where matches don't exist

Multiple Joins

Chain multiple JOIN operations to combine data from 3+ tables in a single query

Knowledge Check

Quick Quiz

Test what you've learned about SQL joins

1 What is the main difference between INNER JOIN and LEFT JOIN?
2 You have customers and orders tables. Some customers have no orders. Which query finds customers WITHOUT any orders?
3 Given: customers (id, name), orders (id, customer_id, amount). What does this query return?
SELECT name FROM customers INNER JOIN orders ON customers.id = orders.customer_id
4 Which join type returns ALL rows from BOTH tables, with NULLs where matches don't exist?
5 You're joining customers → orders → products (3 tables). After the first INNER JOIN, the result has 10 rows. What can you conclude about the second INNER JOIN result?
6 In MySQL, which approach emulates FULL OUTER JOIN (which MySQL doesn't support natively)?
Answer all questions to check your score