LEFT and RIGHT JOIN

LEFT JOIN

The LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right table. If no match exists, NULL values are returned for right table columns.

Basic Syntax

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

How LEFT JOIN Works

customers (left)           orders (right)
+----+--------+            +----+----+--------+
| id | name   |            | id | cid| total  |
+----+--------+            +----+----+--------+
| 1  | Alice  | ---------> | 1  | 1  | 250.00 |
| 2  | Bob    | ---------> | 3  | 2  | 320.00 |
| 3  | Charlie| ---------> | 4  | 3  | 89.99  |
| 4  | Diana  | ---NULL--  | 2  | 1  | 175.50 |
+----+--------+            +----+----+--------+

Result: All 4 customers
- Alice: 2 orders
- Bob: 1 order
- Charlie: 1 order
- Diana: NULL (no orders)

LEFT JOIN Examples

-- All customers with their orders (including those without orders)
SELECT 
    c.name,
    c.email,
    o.order_id,
    o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Customers without orders
SELECT 
    c.name,
    c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Count orders per customer (including zero)
SELECT 
    c.name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

LEFT JOIN with Additional Conditions

-- Condition in ON clause (affects join)
SELECT 
    c.name,
    o.total
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id
    AND o.total > 100;  -- Only includes orders > 100

-- Condition in WHERE clause (filters result)
SELECT 
    c.name,
    o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 100;  -- Excludes customers with no matching orders

LEFT JOIN Best Practices

1. Use LEFT JOIN to include all rows from left table
2. Check for NULL in right table to find non-matching rows
3. Put join conditions in ON for LEFT JOIN
4. Put filter conditions in WHERE to filter results

-- Good: All customers with order summary
SELECT 
    c.name,
    COALESCE(SUM(o.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;

LEFT JOIN is essential when you need all records from the primary table regardless of matches.

RIGHT JOIN

The RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and matching rows from the left table. If no match exists, NULL values are returned for left table columns.

Basic Syntax

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

How RIGHT JOIN Works

orders (left)              customers (right)
+----+----+--------+      +----+--------+
| id | cid| total  |      | id | name   |
+----+----+--------+      +----+--------+
| 1  | 1  | 250.00 | <--- | 1  | Alice  |
| 3  | 2  | 320.00 | <--- | 2  | Bob    |
| 4  | 3  | 89.99  | <--- | 3  | Charlie|
| 2  | 1  | 175.50 |      | 4  | Diana  | <--NULL
+----+----+--------+      +----+--------+

Result: All 4 customers
- Alice: 2 orders
- Bob: 1 order
- Charlie: 1 order
- Diana: NULL (no orders)

RIGHT JOIN Examples

-- All orders with customer info (including orphaned orders)
SELECT 
    o.order_id,
    o.total,
    c.name AS customer_name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

-- Find orders without valid customers (data integrity check)
SELECT 
    o.order_id,
    o.customer_id,
    o.total
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- All customers with order count
SELECT 
    c.name,
    COUNT(o.order_id) AS order_count
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

RIGHT JOIN vs LEFT JOIN

-- These two queries produce the same result:

-- Using LEFT JOIN
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Using RIGHT JOIN (reversed table order)
SELECT c.name, o.total
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

Converting RIGHT JOIN to LEFT JOIN

-- RIGHT JOIN (not supported in all databases)
SELECT c.name, o.total
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

-- Equivalent LEFT JOIN (more portable)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

RIGHT JOIN Best Practices

1. Prefer LEFT JOIN for better portability
2. Use RIGHT JOIN only when it simplifies the query
3. Check for NULLs to find non-matching rows
4. Consider table order for readability

-- Good: Using LEFT JOIN (portable)
SELECT 
    c.name,
    COALESCE(COUNT(o.order_id), 0) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- Also good: RIGHT JOIN when it's clearer
SELECT 
    p.name,
    COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM order_items oi
RIGHT JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name;

While RIGHT JOIN is useful, LEFT JOIN is more commonly used and more portable across database systems.

NULL Handling in Outer Joins

When using LEFT or RIGHT JOINs, NULL values appear for non-matching rows. Proper NULL handling ensures correct results.

Common NULL Scenarios

-- LEFT JOIN returns NULLs for non-matching right table
SELECT 
    c.name,
    o.order_id,  -- NULL if no order
    o.total       -- NULL if no order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Result for customer with no orders:
-- | Diana | NULL | NULL |

Handling NULLs with COALESCE

-- Replace NULLs with default values
SELECT 
    c.name,
    COALESCE(o.order_id, 'No Order') AS order_id,
    COALESCE(o.total, 0) AS total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Aggregate with COALESCE
SELECT 
    c.name,
    COALESCE(SUM(o.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;

Finding Non-Matching Rows

-- Customers without orders
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Products never ordered
SELECT p.name, p.category
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;

-- Employees without departments
SELECT e.name, e.email
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

NULL in Aggregate Functions

-- COUNT(*) counts all rows (including those with NULLs)
SELECT 
    c.name,
    COUNT(*) AS total_rows,
    COUNT(o.order_id) AS orders_with_data
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- SUM ignores NULLs
SELECT 
    c.name,
    SUM(o.total) AS total_spent  -- NULLs excluded
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- To include NULLs as 0
SELECT 
    c.name,
    SUM(COALESCE(o.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;

NULL Handling Best Practices

1. Use COALESCE to provide default values
2. Check IS NULL to find non-matching rows
3. Use COUNT(column) to count only non-NULL values
4. Consider table design to minimize NULLs

-- Good: Comprehensive NULL handling
SELECT 
    c.name,
    COALESCE(c.email, 'No email') AS email,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent,
    CASE 
        WHEN MAX(o.order_date) IS NULL THEN 'Never ordered'
        ELSE MAX(o.order_date)
    END AS last_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email;

Proper NULL handling is crucial for accurate results in outer joins.

Common Anti-patterns in Outer Joins

Avoid these common mistakes when using LEFT JOIN and RIGHT JOIN.

Anti-pattern 1: Wrong NULL Check

-- Wrong: Checking wrong column
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total IS NULL;  -- Wrong! Should check order_id

-- Correct: Check primary key of right table
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Anti-pattern 2: WHERE Clause Filtering

-- Problem: WHERE removes NULL rows
SELECT 
    c.name,
    o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 100;  -- Excludes customers with no orders!

-- Solution: Use ON clause for join conditions
SELECT 
    c.name,
    o.total
FROM customers c
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id
    AND o.total > 100;  -- Keeps all customers

Anti-pattern 3: Unnecessary LEFT JOIN

-- Unnecessary: INNER JOIN would work
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NOT NULL;  -- Effectively an INNER JOIN!

-- Better: Use INNER JOIN when you only want matches
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Anti-pattern 4: Multiple LEFT JOINs with WHERE

-- Problem: WHERE can negate LEFT JOIN benefits
SELECT 
    c.name,
    o.total,
    p.name AS product
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.total > 100;  -- Excludes customers without orders!

-- Solution: Use COALESCE or check NULLs
SELECT 
    c.name,
    COALESCE(o.total, 0) AS total,
    p.name AS product
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.total > 100 OR o.total IS NULL;

Anti-pattern 5: COUNT(*) vs COUNT(column)

-- Problem: COUNT(*) includes rows with NULLs
SELECT 
    c.name,
    COUNT(*) AS order_count  -- Includes 1 for customers with no orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- Solution: COUNT(column) excludes NULLs
SELECT 
    c.name,
    COUNT(o.order_id) AS order_count  -- Correct: 0 for no orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Best Practices Summary

1. Check the correct column for NULL (primary key of right table)
2. Use ON for join conditions, WHERE for filtering
3. Use INNER JOIN when you only want matches
4. Use COUNT(column) to count only non-NULL values
5. Use COALESCE to handle NULLs in results

-- Good: Proper LEFT JOIN usage
SELECT 
    c.name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent,
    MAX(o.order_date) AS last_order
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;

Following these best practices ensures correct and efficient outer join queries.