Joins & Relationships

Objective

Write queries joining multiple tables using INNER, LEFT, RIGHT, and FULL OUTER joins, and understand when to use each.

Tools & Technologies

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
subquery

Key Commands

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

LEFT JOIN

WHERE b.id IS NULL

UNION ALL

Architecture Overview

graph LR subgraph JOIN Types A[Table A] & B[Table B] INNER[INNER JOIN\nOnly matching rows] LEFT[LEFT JOIN\nAll A + matching B] RIGHT[RIGHT JOIN\nMatching A + all B] FULL[FULL OUTER\nAll rows both tables] end style INNER fill:#1a1a2e,stroke:#00d4ff,color:#e0e0e0 style LEFT fill:#1a1a2e,stroke:#00ff88,color:#e0e0e0

Step-by-Step Process

INNER and LEFT JOINs

The most common join types.

-- INNER JOIN: only rows that match in both tables
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.id, u.name;

-- LEFT JOIN: all users, even those with no 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.id, u.name;

Anti-joins and CTEs

Find records with no match, use CTEs for readability.

-- Find users with no orders (anti-join)
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- CTE
WITH active_users AS (
  SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT u.name, COUNT(o.id)
FROM active_users au
JOIN users u ON u.id = au.id
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

Challenges & Solutions

Missing JOIN condition creates a cartesian product — every row with every row
NULL comparisons in WHERE can exclude LEFT JOIN non-matching rows unexpectedly

Key Takeaways

LEFT JOIN + WHERE b.id IS NULL is the anti-join pattern for 'not in' checks
CTEs improve readability but are not always optimized as inline views