SQL Joins

SQL joins are fundamental operations that allow you to combine rows from two or more tables based on related columns. Mastering joins is essential for any database interview, as they form the backbone of relational database queries.

---

Types of Joins

INNER JOIN

Returns only rows that have matching values in both tables. This is the most common join type.

SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Use when: You only need records that exist in both tables. For example, finding all users who have placed orders.

Interview tip: INNER JOIN is the default JOIN in most databases. If you just write JOIN, it's an INNER JOIN.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, and matched rows from the right table. Unmatched rows from the right table contain NULL.

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Use when: You need all records from the left table, even if they don't have matches. Common use case: finding all users and their order totals (including users with no orders).

Interview tip: LEFT JOIN is often used to find "missing" relationships. For example, users who haven't placed orders: WHERE orders.user_id IS NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

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

SELECT users.name, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

Use when: You need all records from the right table. Less commonly used than LEFT JOIN.

Interview tip: RIGHT JOIN can always be rewritten as LEFT JOIN by swapping table order. Most developers prefer LEFT JOIN for consistency.

FULL OUTER JOIN

Returns all rows when there is a match in either table. Unmatched rows contain NULL.

SELECT users.name, orders.total
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

Use when: You need all records from both tables. Useful for finding complete relationships and gaps.

Interview tip: FULL OUTER JOIN is less common but useful for data analysis and finding discrepancies between datasets.

CROSS JOIN

Returns the Cartesian product of both tables (every row from first table combined with every row from second table).

SELECT users.name, products.name
FROM users
CROSS JOIN products;

Use when: You need all possible combinations. Rare in production, but useful for generating test data or creating combinations.

Interview tip: CROSS JOIN can be very expensive (N × M rows). Always question if it's really needed.

---

Join Performance Considerations

Indexes

Ensure join columns are indexed for better performance. The join condition (ON users.id = orders.user_id) should have indexes on both sides.

-- Create indexes for join columns
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_id ON users(id);  -- Usually already indexed as primary key

Join Order

The query planner typically optimizes join order, but understanding it helps:

• Smaller tables first: Join smaller tables before larger ones when possible
• Most selective filters first: Apply WHERE clauses before joins to reduce dataset size

Filter Early

Apply WHERE clauses before joins when possible to reduce the dataset:

-- ❌ Bad: Joins all data, then filters
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- ✅ Better: Filter first, then join
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;

---

Common Patterns

Self-Join

Joining a table to itself, useful for hierarchical data.

-- Find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Interview scenario: "How would you represent a company hierarchy in SQL?" Answer: Self-join on manager_id.

Multiple Joins

Joining three or more tables:

SELECT users.name, orders.total, products.name
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

Interview tip: Always verify join conditions. Missing or incorrect ON clauses can cause Cartesian products.

Anti-Join (NOT EXISTS / NOT IN)

Finding rows that don't have matches:

-- Users who haven't placed orders
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

-- Alternative using NOT EXISTS (often faster)
SELECT u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

---

Interview Questions

1. Beginner Question

Q: What's the difference between INNER JOIN and LEFT JOIN?

A:

• INNER JOIN returns only rows that have matching values in both tables. If a user has no orders, they won't appear in the result.
• LEFT JOIN returns all rows from the left table, and matched rows from the right table. Users without orders will appear with NULL values for order columns.

Example:

-- INNER JOIN: Only users with orders
SELECT u.name, COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- LEFT JOIN: All users, including those with 0 orders
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

2. Intermediate Question

Q: How would you find all customers who have placed orders in the last 30 days but haven't placed any orders in the previous 30 days before that?

A: This requires a self-join or subquery to compare time periods:

SELECT DISTINCT u.id, u.name
FROM users u
INNER JOIN orders o1 ON u.id = o1.user_id
WHERE o1.created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND o1.created_at < CURRENT_DATE
  AND NOT EXISTS (
    SELECT 1 
    FROM orders o2 
    WHERE o2.user_id = u.id
      AND o2.created_at >= CURRENT_DATE - INTERVAL '60 days'
      AND o2.created_at < CURRENT_DATE - INTERVAL '30 days'
  );

Follow-up: How would you optimize this query? (Indexes on user_id and created_at, consider materialized views for analytics)

3. Senior-Level System Question

Q: Design a query system for an e-commerce platform that needs to join user data, order history, product catalog, and inventory. The system handles 10M users, 100M orders, and 1M products. How would you optimize joins at scale?

A:

Architecture considerations:

1. Denormalization: Pre-join frequently accessed data into materialized views or cache layers

   CREATE MATERIALIZED VIEW user_order_summary AS
   SELECT u.id, u.name, COUNT(o.id) as total_orders, SUM(o.total) as lifetime_value
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   GROUP BY u.id, u.name;
   

2. Partitioning: Partition large tables by date or user_id ranges to reduce join scope

   -- Partition orders by month
   CREATE TABLE orders_2024_01 PARTITION OF orders
   FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
   

3. Indexing strategy:

   • Composite indexes on join columns: (user_id, created_at) for orders
   • Covering indexes to avoid table lookups
   • Bitmap indexes for low-cardinality columns

4. Query optimization:

   • Use EXPLAIN ANALYZE to verify join order
   • Consider hash joins for large datasets
   • Use LIMIT early when possible

5. Caching layer: Cache frequently joined results (user + recent orders) in Redis

6. Read replicas: Route read queries to replicas, keeping primary for writes

Trade-offs: Denormalization improves read performance but complicates writes. Partitioning helps with large datasets but requires careful query design.

---

Key Takeaways

• INNER JOIN is the most common join type, returning only matching rows from both tables
• LEFT JOIN preserves all rows from the left table, useful for finding missing relationships
• Always index join columns for performance, especially in production systems
• Filter before joining when possible to reduce dataset size and improve query performance
• Self-joins are powerful for hierarchical data (org charts, comment threads, category trees)
• Multiple joins require careful attention to join conditions to avoid Cartesian products
• Anti-joins (NOT EXISTS) are often more efficient than LEFT JOIN + IS NULL for finding missing relationships
• At scale, consider denormalization, partitioning, and caching to optimize join performance
• Understand query execution plans to verify join order and identify optimization opportunities