Inner_outer_joins

Chapter 13: INNER JOIN & OUTER JOINs

Deep Dive into Join Types

---

13.1 INNER JOIN Fundamentals

INNER JOIN returns only the rows that have matching values in both tables.

                    INNER JOIN Flow
    ========================================================================

    ┌─────────────────┐         ┌─────────────────┐
    │    Table A      │         │    Table B      │
    │  (users)        │         │  (orders)       │
    │                 │         │                 │
    │ ┌─────────────┐ │         │ ┌─────────────┐ │
    │ │ id │ name   │ │         │ │ id │ user_id │ │
    │ ├───┼────────┤ │         │ ├───┼─────────┤ │
    │ │ 1  │ Alice  │ │         │ │ 1  │    1    │ │
    │ │ 2  │ Bob    │ │         │ │ 2  │    1    │ │
    │ │ 3  │ Charlie│ │         │ │ 3  │    2    │ │
    │ │ 4  │ David  │ │         │ │ 4  │    5    │ │ ← No match
    │ └─────────────┘ │         │ └─────────────┘ │
    └────────┬────────┘         └────────┬────────┘
             │                           │
             └───────────┬───────────────┘
                         │
                         ▼
    ┌─────────────────────────────────────────┐
    │           INNER JOIN Result             │
    │  (Only matching rows from both tables)  │
    │                                         │
    │  user_id │ name  │ order_id │ amount    │
    │  ────────┼───────┼──────────┼────────   │
    │     1    │ Alice │    1     │ 100.00    │
    │     1    │ Alice │    2     │  50.00    │
    │     2    │ Bob   │    3     │ 200.00    │
    │                                         │
    └─────────────────────────────────────────┘

Basic INNER JOIN Syntax

-- Sample tables
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_total DECIMAL(10,2),
    order_date DATE DEFAULT CURRENT_DATE
);

-- Insert sample data
INSERT INTO customers (name, email) VALUES
    ('Alice Johnson', 'alice@example.com'),
    ('Bob Smith', 'bob@example.com'),
    ('Charlie Brown', 'charlie@example.com');

INSERT INTO orders (customer_id, order_total) VALUES
    (1, 150.00),
    (1, 75.50),
    (2, 200.00),
    (2, 100.00),
    (3, 50.00);

-- Basic INNER JOIN
SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.order_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Result: Only customers with orders are returned

---

13.2 LEFT OUTER JOIN

LEFT OUTER JOIN returns all rows from the left table and matched rows from the right table. NULL values for columns where no match exists.

                    LEFT JOIN Flow
    ========================================================================

    ┌─────────────────┐         ┌─────────────────┐
    │    Table A      │         │    Table B      │
    │  (customers)    │         │  (orders)       │
    │                 │         │                 │
    │ ┌─────────────┐ │         │ ┌─────────────┐ │
    │ │ id │ name   │ │         │ │ id │ cust_id │ │
    │ ├───┼────────┤ │         │ ├───┼─────────┤ │
    │ │ 1  │ Alice  │ │         │ │ 1  │    1    │ │
    │ │ 2  │ Bob    │ │         │ │ 2  │    1    │ │
    │ │ 3  │ Charlie│ │         │ │ 3  │    2    │ │
    │ │ 4  │ David  │ │         │ │   │         │ │ ← No orders
    │ └─────────────┘ │         │ └─────────────┘ │
    └────────┬────────┘         └────────┬────────┘
             │                           │
             └───────────┬───────────────┘
                         │
                         ▼
    ┌─────────────────────────────────────────┐
    │            LEFT JOIN Result             │
    │  (All from left + matches from right)   │
    │                                         │
    │  cust_id │ name   │ order_id │ amount   │
    │  ────────┼────────┼──────────┼────────  │
    │     1    │ Alice  │    1     │ 100.00   │
    │     1    │ Alice  │    2     │  50.00   │
    │     2    │ Bob    │    3     │ 200.00   │
    │     3    │ Charlie│  NULL    │  NULL    │ ← No match
    │     4    │ David  │  NULL    │  NULL    │ ← No match
    │                                         │
    └─────────────────────────────────────────┘

LEFT JOIN Examples

-- Find all customers, even those without orders
SELECT
    c.customer_id,
    c.name,
    COALESCE(COUNT(o.order_id), 0) as total_orders,
    COALESCE(SUM(o.order_total), 0) as total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

-- Find customers who never placed an order
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

---

13.3 RIGHT OUTER JOIN

RIGHT OUTER JOIN returns all rows from the right table and matched rows from the left table.

                    RIGHT JOIN Flow
    ========================================================================

    ┌─────────────────┐         ┌─────────────────┐
    │    Table A      │         │    Table B      │
    │  (customers)    │         │  (orders)       │
    │                 │         │                 │
    │ ┌─────────────┐ │         │ ┌─────────────┐ │
    │ │ id │ name   │ │         │ │ id │ cust_id │ │
    │ ├───┼────────┤ │         │ ├───┼─────────┤ │
    │ │ 1  │ Alice  │ │         │ │ 1  │    1    │ │
    │ │ 2  │ Bob    │ │         │ │ 2  │    1    │ │
    │ │   │         │ │ ← No    │ │ 3  │    2    │ │
    │ │   │         │ │  match  │ │ 4  │    5    │ │ ← Orphan
    │ └─────────────┘ │         │ └─────────────┘ │
    └────────┬────────┘         └────────┬────────┘
             │                           │
             └───────────┬───────────────┘
                         │
                         ▼
    ┌─────────────────────────────────────────┐
    │           RIGHT JOIN Result             │
    │  (All from right + matches from left)   │
    │                                         │
    │  cust_id │ name   │ order_id │ amount   │
    │  ────────┼────────┼──────────┼────────  │
    │     1    │ Alice  │    1     │ 100.00   │
    │     1    │ Alice  │    2     │  50.00   │
    │     2    │ Bob    │    3     │ 200.00   │
    │     5    │ NULL   │    4     │  75.00   │ ← No customer
    │                                         │
    └─────────────────────────────────────────┘

RIGHT JOIN Examples

-- Find all orders, including those without valid customers
SELECT
    o.order_id,
    o.order_total,
    c.name as customer_name
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

---

13.4 FULL OUTER JOIN

FULL OUTER JOIN returns all rows when there’s a match in either left or right table.

                    FULL JOIN Flow
    ========================================================================

    ┌─────────────────┐         ┌─────────────────┐
    │    Table A      │         │    Table B      │
    │  (customers)    │         │  (orders)       │
    │                 │         │                 │
    │ ┌─────────────┐ │         │ ┌─────────────┐ │
    │ │ id │ name   │ │         │ │ id │ cust_id │ │
    │ ├───┼────────┤ │         │ ├───┼─────────┤ │
    │ │ 1  │ Alice  │ │         │ │ 1  │    1    │ │
    │ │ 2  │ Bob    │ │         │ │ 2  │    1    │ │
    │ │ 3  │ Charlie│ │         │ │ 3  │    2    │ │
    │ │ 4  │ David  │ │         │ │ 4  │    5    │ │
    │ └─────────────┘ │         │ └─────────────┘ │
    └────────┬────────┘         └────────┬────────┘
             │                           │
             └───────────┬───────────────┘
                         │
                         ▼
    ┌─────────────────────────────────────────┐
    │          FULL OUTER JOIN Result         │
    │  (All rows from both tables)             │
    │                                         │
    │  cust_id │ name   │ order_id │ amount   │
    │  ────────┼────────┼──────────┼────────  │
    │     1    │ Alice  │    1     │ 100.00   │
    │     1    │ Alice  │    2     │  50.00   │
    │     2    │ Bob    │    3     │ 200.00   │
    │     3    │ Charlie│  NULL    │  NULL    │
    │     4    │ David  │  NULL    │  NULL    │
    │     5    │ NULL   │    4     │  75.00   │
    │                                         │
    └─────────────────────────────────────────┘

FULL JOIN Examples

-- Find all customers and orders, showing relationships
SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.order_total
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

-- Find orphaned orders and customers without orders in one query
SELECT
    CASE
        WHEN c.customer_id IS NULL THEN 'Orphaned Order'
        WHEN o.order_id IS NULL THEN 'No Orders'
    END as data_issue,
    COALESCE(c.name, 'N/A') as customer_name,
    COALESCE(o.order_id::text, 'N/A') as order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.order_id IS NULL;

---

13.5 Multiple JOINs

You can chain multiple JOINs together to connect more than two tables.

-- Extended schema with multiple related tables
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INTEGER
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER,
    unit_price DECIMAL(10,2)
);

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(50)
);

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

INSERT INTO products (product_name, category_id) VALUES
    ('Laptop', 1), ('Phone', 1), ('Shirt', 2), ('Book', 3);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 2, 999.99),
    (1, 3, 3, 29.99),
    (2, 2, 1, 599.99),
    (3, 4, 5, 19.99);

-- Multiple JOINs example
SELECT
    c.name as customer_name,
    o.order_id,
    p.product_name,
    cat.category_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) as line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN categories cat ON p.category_id = cat.category_id
ORDER BY o.order_id, p.product_name;

---

13.6 Join Conditions

Using ON vs USING vs NATURAL JOIN

-- ON clause (most flexible)
SELECT *
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
    AND o.order_total > 100;  -- Additional condition

-- USING clause (when columns have same name)
SELECT *
FROM customers c
INNER JOIN orders o USING (customer_id);

-- NATURAL JOIN (automatic matching on same-named columns)
SELECT *
FROM customers c
NATURAL JOIN orders o;  -- Joins on customer_id automatically

-- Multiple conditions
SELECT *
FROM table_a a
LEFT JOIN table_b b
    ON a.id = b.a_id
    AND b.status = 'active'
    AND b.created_at > '2024-01-01';

---

13.7 Practical Examples

E-commerce Analytics

-- Comprehensive customer order analysis
SELECT
    c.customer_id,
    c.name,
    c.email,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(o.order_total) as lifetime_value,
    AVG(o.order_total) as avg_order_value,
    MIN(o.order_date) as first_order_date,
    MAX(o.order_date) as last_order_date,
    CASE
        WHEN COUNT(o.order_id) >= 10 THEN 'VIP'
        WHEN COUNT(o.order_id) >= 5 THEN 'Premium'
        WHEN COUNT(o.order_id) >= 1 THEN 'Regular'
        ELSE 'New'
    END as customer_tier
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email
ORDER BY lifetime_value DESC;

---

13.8 Best Practices

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

    ✓ DO:
    ───────────────
    • Use explicit JOIN syntax (not comma-separated FROM)
    • Always specify join conditions in ON clause
    • Use table aliases for readability
    • Consider join order for performance
    • Index columns used in JOIN conditions

    ✗ DON'T:
    ───────────────
    • Mix old-style and new-style joins
    • Forget to handle NULL values with COALESCE
    • Join on expressions that prevent index usage
    • Create Cartesian products by forgetting conditions
    • Join unnecessary tables

---

Summary

Join Type	Returns
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from left + matches from right
RIGHT JOIN	All rows from right + matches from left
FULL JOIN	All rows from both tables
CROSS JOIN	Cartesian product of both tables

---

Next: Chapter 14: Self JOINs & Cross JOINs