Joins

Chapter 12: Understanding JOINs

Combining Data from Multiple Tables

---

12.1 JOIN Fundamentals

JOINs are essential for combining related data from multiple tables. Understanding the different types and when to use each is crucial for effective SQL querying.

                    JOIN Types Overview
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                        JOIN Types                                    │
    │                                                                      │
    │  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐                │
    │  │   INNER    │  │   LEFT     │  │   RIGHT    │                │
    │  │   JOIN     │  │   JOIN     │  │   JOIN     │                │
    │  │             │  │             │  │             │                │
    │  │ Returns     │  │ Returns all │  │ Returns all │                │
    │  │ matching    │  │ from left   │  │ from right  │                │
    │  │ rows        │  │ + matches   │  │ + matches   │                │
    │  └─────────────┘  └─────────────┘  └─────────────┘                │
    │                                                                      │
    │  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐                │
    │  │   FULL     │  │   CROSS    │  │   SELF     │                │
    │  │   OUTER    │  │   JOIN     │  │   JOIN     │                │
    │  │             │  │             │  │             │                │
    │  │ Returns     │  │ Returns     │  │ Joins a    │                │
    │  │ all rows    │  │ Cartesian   │  │ table to   │                │
    │  │ + matches   │  │ product     │  │ itself     │                │
    │  └─────────────┘  └─────────────┘  └─────────────┘                │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

12.2 Sample Data Setup

-- Create tables for JOIN examples
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category_id INTEGER REFERENCES categories(id),
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    order_date DATE DEFAULT CURRENT_DATE
);

-- Insert sample data
INSERT INTO categories (name) VALUES
    ('Electronics'), ('Clothing'), ('Books');

INSERT INTO products (name, category_id, price) VALUES
    ('Laptop', 1, 999.99),
    ('Phone', 1, 599.99),
    ('Shirt', 2, 29.99),
    ('Book', 3, 19.99),
    ('Tablet', 1, 399.99);

INSERT INTO orders (product_id, quantity, order_date) VALUES
    (1, 2, '2024-01-15'),
    (2, 1, '2024-01-16'),
    (3, 3, '2024-01-17'),
    (1, 1, '2024-01-18');

---

12.3 INNER JOIN

Returns only rows that have matching values in both tables.

-- Basic INNER JOIN
SELECT
    p.name AS product_name,
    c.name AS category_name,
    p.price
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

-- Equivalent (INNER is default)
SELECT p.name, c.name
FROM products p
JOIN categories c ON p.category_id = c.id;

-- INNER JOIN with WHERE clause
SELECT
    o.id AS order_id,
    p.name AS product_name,
    o.quantity,
    o.order_date
FROM orders o
INNER JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2024-01-16';

-- Multiple INNER JOINs
SELECT
    o.id,
    p.name AS product,
    c.name AS category,
    o.quantity,
    o.order_date
FROM orders o
INNER JOIN products p ON o.product_id = p.id
INNER JOIN categories c ON p.category_id = c.id;

---

12.4 LEFT JOIN

Returns all rows from the left table and matched rows from the right table.

-- Basic LEFT JOIN
SELECT
    c.name AS category,
    p.name AS product
FROM categories c
LEFT JOIN products p ON c.id = p.category_id;

-- LEFT JOIN to find products without orders
SELECT
    p.id,
    p.name,
    p.price
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL;

-- LEFT JOIN with aggregation
SELECT
    c.name AS category,
    COUNT(p.id) AS product_count,
    COALESCE(SUM(p.price), 0) AS total_value
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name;

-- Multiple LEFT JOINs
SELECT
    c.name AS category,
    p.name AS product,
    o.quantity,
    o.order_date
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
LEFT JOIN orders o ON p.id = o.product_id;

---

12.5 RIGHT JOIN

Returns all rows from the right table and matched rows from the left table.

-- Basic RIGHT JOIN
SELECT
    p.name AS product,
    c.name AS category
FROM products p
RIGHT JOIN categories c ON p.category_id = c.id;

-- RIGHT JOIN to find categories without products
SELECT
    c.id,
    c.name
FROM products p
RIGHT JOIN categories c ON p.category_id = c.id
WHERE p.id IS NULL;

---

12.6 FULL OUTER JOIN

Returns all rows when there’s a match in either table.

-- Basic FULL OUTER JOIN
SELECT
    c.name AS category,
    p.name AS product
FROM categories c
FULL OUTER JOIN products p ON c.id = p.category_id;

-- FULL OUTER JOIN to find unmatched records
SELECT
    COALESCE(c.id, p.category_id) AS id,
    c.name AS category_name,
    p.name AS product_name
FROM categories c
FULL OUTER JOIN products p ON c.id = p.category_id
WHERE c.id IS NULL OR p.id IS NULL;

-- FULL OUTER JOIN with aggregation
SELECT
    c.name AS category,
    COUNT(p.id) AS products,
    COUNT(o.id) AS orders
FROM categories c
FULL OUTER JOIN products p ON c.id = p.category_id
FULL OUTER JOIN orders o ON p.id = o.product_id
GROUP BY c.name;

---

12.7 CROSS JOIN

Returns Cartesian product of two tables.

-- Basic CROSS JOIN
SELECT c.name, p.name
FROM categories c
CROSS JOIN products p;

-- Equivalent to:
SELECT c.name, p.name
FROM categories c, products p;

-- Practical use: generating combinations
CREATE TABLE sizes (id SERIAL, name VARCHAR(10));
CREATE TABLE colors (id SERIAL, name VARCHAR(10));

INSERT INTO sizes (name) VALUES ('S'), ('M'), ('L'), ('XL');
INSERT INTO colors (name) VALUES ('Red'), ('Blue'), ('Green');

-- Generate all size/color combinations
SELECT s.name AS size, c.name AS color
FROM sizes s
CROSS JOIN colors c;

---

12.8 SELF JOIN

Joins a table to itself.

-- Create employee table for self-join example
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INTEGER REFERENCES employees(id),
    department VARCHAR(50)
);

INSERT INTO employees (name, manager_id, department) VALUES
    ('CEO', NULL, 'Executive'),
    ('VP Sales', 1, 'Sales'),
    ('VP Engineering', 1, 'Engineering'),
    ('Sales Manager', 2, 'Sales'),
    ('Engineer Manager', 3, 'Engineering'),
    ('Sales Rep', 4, 'Sales'),
    ('Developer', 5, 'Engineering');

-- Basic SELF JOIN: Employees with their managers
SELECT
    e.name AS employee,
    e.department,
    m.name AS manager,
    m.department AS manager_dept
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Self JOIN to find co-workers (same manager)
SELECT
    e1.name AS employee1,
    e2.name AS employee2,
    e1.manager_id
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id
WHERE e1.id < e2.id
ORDER BY e1.manager_id;

-- Recursive hierarchy
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, manager_id;

---

12.9 Advanced JOIN Patterns

-- JOIN with complex conditions
SELECT
    o.id,
    p.name,
    o.quantity,
    o.order_date
FROM orders o
JOIN products p ON o.product_id = p.id
    AND p.price > 50
    AND o.quantity > 1
WHERE o.order_date >= '2024-01-01';

-- Using JOIN with subquery
SELECT
    c.name,
    p.name,
    p.price
FROM categories c
JOIN (
    SELECT * FROM products
    WHERE price > 100
) p ON c.id = p.category_id;

-- JOIN with calculated join condition
SELECT
    o1.id AS order1,
    o2.id AS order2,
    o1.product_id,
    o1.order_date AS date1,
    o2.order_date AS date2
FROM orders o1
JOIN orders o2 ON o1.product_id = o2.product_id
    AND o1.id < o2.id
WHERE o1.product_id = 1;

-- Multiple JOINs with different types
SELECT
    c.name AS category,
    p.name AS product,
    o.quantity,
    o.order_date
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
LEFT JOIN orders o ON p.id = o.product_id
ORDER BY c.name, p.name;

---

12.10 JOIN Performance

-- Use EXPLAIN to analyze JOIN performance
EXPLAIN ANALYZE
SELECT p.name, c.name
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

-- Create indexes for JOIN columns
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_orders_product ON orders(product_id);

-- Check query plan for missing indexes
EXPLAIN
SELECT o.*, p.*
FROM orders o
JOIN products p ON o.product_id = p.id;

-- Use covering index
CREATE INDEX idx_products_covering ON products(category_id) INCLUDE (name, price);

---

12.11 Best Practices

                    JOIN Best Practices
    ========================================================================

    1. Use Explicit JOIN Syntax
       ┌──────────────────────────────────────────────────────────────┐
       │  ✓ SELECT * FROM a JOIN b ON a.id = b.a_id                  │
       │  ✗ SELECT * FROM a, b WHERE a.id = b.a_id (old syntax)       │
       └──────────────────────────────────────────────────────────────┘

    2. Index Foreign Keys
       ┌──────────────────────────────────────────────────────────────┐
       │  • Always index columns used in JOIN conditions             │
       │  • This dramatically improves join performance              │
       └──────────────────────────────────────────────────────────────┘

    3. Choose Correct JOIN Type
       ┌──────────────────────────────────────────────────────────────┐
       │  • INNER JOIN: When you need only matches                   │
       │  • LEFT JOIN: When you need all from one table              │
       │  • FULL OUTER: When you need all from both tables          │
       └──────────────────────────────────────────────────────────────┘

    4. Use Aliases
       ┌──────────────────────────────────────────────────────────────┐
       │  • Makes queries more readable                              │
       │  • Required when joining same table (self-join)             │
       └──────────────────────────────────────────────────────────────┘

    5. Order Tables Strategically
       ┌──────────────────────────────────────────────────────────────┐
       │  • Put larger tables on the right with LEFT JOIN            │
       │  • Put selective tables first with INNER JOIN               │
       └──────────────────────────────────────────────────────────────┘
    ========================================================================

---

Next Chapter

Chapter 15: Subqueries

---

Last Updated: February 2026