Real data is spread across multiple tables. Joins combine rows from two or more tables based on a related column. Once you understand joins, you can answer almost any question your data holds.

Why Joins?#

With separate users and orders tables, you can't answer "what did Alice order?" with a single table. A join combines them:

sql

INNER JOIN#

Returns only rows that have a match in both tables. The most common join.

sql
text

LEFT JOIN (LEFT OUTER JOIN)#

Returns all rows from the left table, plus matching rows from the right. Where there's no match, right-side columns are NULL.

sql
text

Use LEFT JOIN when you want to keep all rows from the left table regardless of matches.

Watch the Join Run#

The difference between the two joins is easiest to see on a question someone actually asks. A shop has a customers table and an orders table, and the marketing team wants to email a coupon to everyone who signed up but never bought anything.

sql

Below, both joins run side by side over the same two tables. Each customer's id is matched against every order's customer_id (that is all the ON clause does), and their rows slide into both results at once. A customer who bought something lands in both. A customer with no order drops out of the INNER JOIN and stays in the LEFT JOIN, with NULL where the item would be.

Those NULL rows are the answer: add WHERE o.id IS NULL to the query above and only the customers who never ordered are left. An INNER JOIN can never find them, because it throws them away before you get to ask.

RIGHT JOIN#

Same as LEFT JOIN but keeps all rows from the right table. Rarely used, since you can always rewrite it as a LEFT JOIN by swapping the table order.

FULL OUTER JOIN#

Returns all rows from both tables. NULLs fill in where there's no match on either side.

sql

CROSS JOIN#

Every row from the left table paired with every row from the right: a Cartesian product. Rarely intentional; usually a mistake.

sql

Self Join#

A table joined to itself. Useful for hierarchical or comparative data.

sql

Multiple Joins#

sql

The N+1 Query Problem#

Joins also save you from asking the same question a hundred times. A blog's home page lists 100 posts with each author's name. The code most ORMs write by default fetches the posts, then loops over them:

sql

That is 1 + N queries, and every one of them is a round trip from the application to the database. Each trip is cheap, but they add up: below, a page of five posts makes six trips; a page of 100 makes 101, and at 2 ms a trip that is 202 ms before the database has done any real work. The same page as one join is one trip:

sql

Batching the lookups (WHERE id IN (7, 2, 5, ...)) gets the same page in two trips; most ORMs offer this as eager loading (include, joinedload, select_related).

Subqueries#

A subquery is a SELECT inside another SELECT. Useful when the condition depends on an aggregate.

sql

CTEs (Common Table Expressions)#

CTEs make complex queries readable by naming intermediate results.

sql

Window Functions#

Window functions compute values across a set of rows related to the current row, without collapsing them into groups.

sql

Common window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM(), AVG().

Join Visual Summary#

text

What to Remember#

  • INNER JOIN: only matching rows from both tables
  • LEFT JOIN: all rows from the left table, NULLs for unmatched right side
  • Join on foreign key = primary key: ON child.fk = parent.pk
  • Subqueries in WHERE / HAVING allow filtering on aggregated or derived values
  • CTEs make multi-step queries readable; think of them as named temporary views
  • Window functions compute per-row analytics without collapsing the result set