JOINs and Query Shape

A join combines rows from more than one table. In application code, joins usually appear when one entity references another: orders belong to users, comments belong to posts, invoices belong to accounts.

SELECT users.email, orders.total
FROM users
JOIN orders ON orders.user_id = users.id
WHERE orders.status = 'paid';

The important part is the ON condition. It tells the database how rows relate. If the condition is wrong or missing, the result can multiply into far more rows than expected.

INNER JOIN vs LEFT JOIN

JOIN usually means an inner join: keep rows that match on both sides.

LEFT JOIN keeps every row from the left table, even when the right table has no match:

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

This is useful for "show users even if they have no orders", but it also introduces NULL values on the right side.

Duplicates are often a query shape issue

If one user has five orders, joining users to orders returns five rows for that user. That is not a database bug. It is the shape of the relationship.

When duplicate-looking rows appear, ask:

• Is this a one-to-many relationship?
• Should the query aggregate rows instead?
• Is the join condition missing part of a composite key?
• Did a LEFT JOIN become an inner join because a right-side filter moved into WHERE?

Key takeaway

Read joins as row multiplication rules. A formatter can make join blocks visible, but you still need to reason about cardinality: one-to-one, one-to-many, and optional relationships.

Use the SQL Formatter to make each JOIN and ON condition visible before reviewing query results.