Join Types Explained

Join Types Explained

Imagine you run a coffee shop and need to handle three situations every day: customers who are members and have made purchases (both tables have data), members who haven't made purchases (left table has data, right table doesn't), and customers who purchased but didn't register as members (right table has data, left table doesn't). SQL provides different types of JOINs to precisely handle these (and more) scenarios.

1. Core Concepts

Overview of Six Join Types

Understanding JOINs with Venn Diagrams

Use two circles A and B to represent two tables:

    ┌───────┐   ┌───────┐
    │   A   │   │   B   │
    │       │   │       │
    │   ┌───┼───┼───┐   │
    │   │ A ∩ B │   │   │
    │   └───┼───┼───┘   │
    │       │   │       │
    └───────┘   └───────┘

    INNER JOIN  → A ∩ B (intersection)
    LEFT JOIN   → A (all A + A∩B)
    RIGHT JOIN  → B (all B + A∩B)
    FULL JOIN   → A ∪ B (union)
    CROSS JOIN  → A × B (all combinations)

INNER JOIN

Retains only rows that match in both tables. Covered in detail in the previous lesson, so we won't repeat it here.

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN

Returns all rows from the left table, with columns from the right table showing NULL where there's no match. The left table is the first table after FROM.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Output (note Zhou Jiu's department is NULL):

name    department
------  ----------
Zhang San Technology
Li Si   Technology
Wang Wu Marketing
Zhao Liu Finance
Qian Qi Technology
Sun Ba  Marketing
Zhou Jiu NULL
Wu Shi  Finance

RIGHT JOIN

Returns all rows from the right table, with columns from the left table showing NULL where there's no match. The right table is the table after JOIN.

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- Equivalent to RIGHT JOIN departments d ON ...
SELECT e.name, d.name AS department
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;

FULL OUTER JOIN

Returns all rows from both tables, showing NULL where there's no match. It's equivalent to the union of LEFT JOIN and RIGHT JOIN.

-- Syntax (supported by MySQL/PostgreSQL/SQL Server)
SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- Simulate FULL OUTER JOIN with LEFT JOIN UNION RIGHT JOIN
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- Equivalent FULL OUTER JOIN in SQLite
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name AS department
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;

CROSS JOIN

Returns the Cartesian product of two tables—every combination of each row from the left table with each row from the right table. Result row count = left table rows × right table rows.

SELECT e.name, d.name AS department
FROM employees e
CROSS JOIN departments d;

If employees has 8 rows and departments has 4 rows, the result will have 8 × 4 = 32 rows.

SELF JOIN

A table joined with itself. Commonly used to query "hierarchical relationships" or "pairings within the same table," such as employees and their managers.

-- Assume the employees table has a manager_id field
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

JOIN Selection Decision Tree

When facing business requirements, choose the join type based on the following logic:

Need to join two tables?
├── Only matched data → INNER JOIN
├── Keep all data from left table → LEFT JOIN
├── Keep all data from right table → RIGHT JOIN
├── Keep all data from both tables → FULL OUTER JOIN
├── Need all combinations → CROSS JOIN
└── Table relates to itself → SELF JOIN

2. Basic Syntax/Usage

LEFT JOIN Syntax

SELECT column_name
FROM table_A
LEFT JOIN table_B ON table_A.column = table_B.column;

RIGHT JOIN Syntax

SELECT column_name
FROM table_A
RIGHT JOIN table_B ON table_A.column = table_B.column;

CROSS JOIN Syntax

SELECT column_name
FROM table_A
CROSS JOIN table_B;
-- CROSS JOIN does not require an ON condition

SELF JOIN Syntax

SELECT a.column, b.column
FROM table_A a
JOIN table_A b ON a.some_column = b.some_column;

Example: Find Employees Without a Department (Difficulty ⭐)

Use LEFT JOIN to find all employees, including those not assigned to a department:

SELECT e.name AS employee, 
       COALESCE(d.name, 'Unassigned') AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Output:

employee  department
--------  ----------
Zhang San Technology
Li Si   Technology
Wang Wu Marketing
Zhao Liu Finance
Qian Qi Technology
Sun Ba  Marketing
Zhou Jiu Unassigned
Wu Shi  Finance

COALESCE(d.name, 'Unassigned') means: if d.name is NULL, display "Unassigned".

Example: Find Products With No Orders (Difficulty ⭐⭐)

The products table has 8 products, but not every product has orders. Use LEFT JOIN to find "zero-sales" products:

SELECT p.name AS product, p.category, p.price,
       COUNT(o.id) AS order_count
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
GROUP BY p.id, p.name, p.category, p.price
HAVING COUNT(o.id) = 0;

Output (depends on data; some products may have no orders):

product  category  price     order_count
-------  --------  --------  -----------
Mac Mini Computer  4499.00   0

Example: Self Join for Employees and Managers (Difficulty ⭐⭐⭐)

Assume the employees table has a manager_id field. Query each employee and their manager:

-- First add manager_id field and data
ALTER TABLE employees ADD COLUMN manager_id INTEGER;

UPDATE employees SET manager_id = 5 WHERE id IN (1, 2, 3, 6);
UPDATE employees SET manager_id = NULL WHERE id = 5;

-- Self join query
SELECT e.name AS employee, 
       COALESCE(m.name, 'No Manager') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Output:

employee  manager
--------  --------
Zhang San Qian Qi
Li Si   Qian Qi
Wang Wu Qian Qi
Zhao Liu No Manager
Qian Qi No Manager
Sun Ba  Qian Qi
Zhou Jiu No Manager
Wu Shi  No Manager

3. Common Use Cases

Case 1: Generate Department-Employee Matrix Report

Use CROSS JOIN to generate a complete "every department × every employee" matrix:

SELECT d.name AS department, e.name AS employee
FROM departments d
CROSS JOIN employees e
WHERE e.department_id IS NOT NULL
ORDER BY d.name, e.name;

This is useful for scenarios like generating "attendance sheets for all staff."

Case 2: Find Employee Pairs in the Same Department

Use SELF JOIN to find all employee pairs in the same department:

SELECT e1.name AS employee1, 
       e2.name AS employee2, 
       d.name AS department
FROM employees e1
INNER JOIN employees e2 ON e1.department_id = e2.department_id 
    AND e1.id < e2.id
INNER JOIN departments d ON e1.department_id = d.id;

Output:

employee1  employee2  department
---------  ---------  ----------
Zhang San  Li Si      Technology
Zhang San  Qian Qi    Technology
Li Si      Qian Qi    Technology
Wang Wu    Sun Ba     Marketing
Zhao Liu   Wu Shi     Finance

❓ FAQ

Q: When do LEFT JOIN and INNER JOIN produce the same results? A: When there are no NULL foreign key values in the left table (i.e., all rows find a match in the right table), LEFT JOIN and INNER JOIN produce identical results. However, LEFT JOIN additionally retains rows from the left table that have no match in the right table.

Q: What if SQLite doesn't support RIGHT JOIN and FULL OUTER JOIN? A: RIGHT JOIN can be implemented by swapping the table order and using LEFT JOIN. FULL OUTER JOIN can be simulated with LEFT JOIN + UNION + LEFT JOIN. In practice, RIGHT JOIN is rarely used—LEFT JOIN is sufficient in most cases.

Q: Can CROSS JOIN be slow? A: If two tables each have 1000 rows, CROSS JOIN will produce 1 million result rows. So use it very carefully, ensuring the data volume is manageable. CROSS JOIN is often used with WHERE conditions, which effectively makes it equivalent to INNER JOIN.

Q: What are the practical uses of SELF JOIN? A: SELF JOIN is commonly used for handling hierarchical data (e.g., organizational structures, comment replies, category trees) and for finding "pairs within the same group" (e.g., employees in the same department, customers in the same city). Whenever you need "row-to-row comparison within the same table," SELF JOIN may be useful.

📖 Summary

• INNER JOIN returns only matching rows, LEFT JOIN preserves all rows from the left table, RIGHT JOIN preserves all rows from the right table
• FULL OUTER JOIN returns the union of both tables, but SQLite doesn't support it—use LEFT JOIN + UNION to simulate
• CROSS JOIN returns the Cartesian product; use cautiously with large datasets
• SELF JOIN connects a table with itself, suitable for hierarchical relationships and same-group pairings
• Join selection decision tree: use INNER for matching, LEFT/RIGHT for preservation, FULL for everything, CROSS for combinations
• SQLite doesn't support RIGHT JOIN and FULL OUTER JOIN, but they can be achieved by swapping table order and combining with UNION

📝 Exercises

Exercise 1 (⭐): Write a query to display all departments and their employee counts, including departments with no employees. Sort by employee count in descending order.

Exercise 2 (⭐⭐): Write a query to find all "customers who have never placed an order." Assuming we need a customers table, you can simulate using the customer_name field in the orders table—find which customers appear only in the orders table and which products have never been ordered.

Exercise 3 (⭐⭐⭐): Write a query using SELF JOIN to find "employee pairs with adjacent hire dates"—the two employees with the closest hire dates (hint: use the julianday() function to calculate date differences).

Next Lesson

👉 09-subquery - Subqueries: Learn to use subqueries in WHERE, FROM, and SELECT, as well as EXISTS/NOT EXISTS!

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us