JOIN

Explanation

JOINs are SQL's way of combining data from multiple tables. The core operation: for each row in table A, find matching rows in table B where a specified condition is true (usually ON a.foreign_key = b.primary_key), and combine them into a result row. JOIN types: INNER JOIN returns only rows where a match exists in both tables (orders with a valid user_id). LEFT JOIN returns all rows from the left table plus matching rows from the right (all users, with their orders if they have any — users with no orders appear with NULL order columns). RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN returns all rows from both tables with NULLs where no match exists. CROSS JOIN returns the Cartesian product (every combination of rows from both tables — usually accidental and expensive). Performance: JOINs on indexed columns are fast (O(n log n) merge join or O(n) hash join). JOINs on un-indexed columns are slow (O(n*m) nested loop join). Always add indexes to JOIN columns (typically the foreign key column). N+1 query problem: executing a separate query for each row in a result set is O(n) database round trips. Example: fetch 100 users, then for each user fetch their posts — that's 101 queries. Solve with a JOIN that fetches all users and their posts in one query, or use DataLoader (for GraphQL) to batch the post queries into one.

Code Example

-- SQL JOIN types with examples

-- INNER JOIN: only users who have orders
SELECT u.name, o.total_cents, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY o.created_at DESC;

-- LEFT JOIN: all users, with their order count (0 for users 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
ORDER BY order_count DESC;

-- Multi-table JOIN: users, their orders, and each order's items
SELECT u.name, o.id AS order_id, p.name AS product, oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed';

-- Avoiding N+1: one JOIN vs 100 separate queries
-- N+1 (bad): for each user, select their latest order
-- Solution: window function + single query
SELECT DISTINCT ON (u.id)
  u.name, o.total_cents AS latest_order_total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.created_at DESC;

Why It Matters for Engineers

JOINs are the most powerful feature of relational databases and the primary reason SQL databases remain the default choice for applications with related data. Understanding JOINs — and the N+1 problem — is essential for writing efficient database queries and avoiding the performance pitfalls that plague ORMs used without understanding the SQL they generate. The N+1 problem is one of the most common and impactful performance bugs in web applications: an API that runs 500 database queries per request instead of 5 will not survive production traffic. Recognizing and solving N+1 problems requires understanding JOINs.