Topic: PostgreSQL

Before JOIN Syntax, Count the Relationship

Decide what one result row means and whether unmatched data stays first. Then choose INNER JOIN or LEFT JOIN without mistaking normal fanout for duplicates.

A JOIN can be syntactically correct and still return more rows than expected. It is easy to call them duplicates too soon.

If one customer has three orders, joining customers to orders should show that customer’s name three times. One row now means “one order with its customer,” not “one customer.” Miss that change and DISTINCT, GROUP BY, or another index can become an attempt to hide the wrong thing.

PostgreSQL describes a join as pairing rows from two tables according to a condition. Each left-side row forms a result row with every right-side row that satisfies ON. A recent r/learnSQL discussion records one example: three orders became fourteen rows because the relationship had not been counted first.

Decide the result grain first

Start with two tables:

customers(id, name)
orders(id, customer_id, total_cents, status)

orders.customer_id points to customers.id. This is a one-to-many relationship: an order belongs to one customer, while a customer can have many orders.

This query therefore has one order per row:

SELECT c.name, o.id, o.total_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id;

If Ada has three orders, Ada appears three times because each order matches her. PostgreSQL did not copy the customer. The query returns order detail.

Before writing a JOIN, turn the requirement into a few questions:

• Should one row represent an order or a customer?
• Should a customer with no orders remain in the result?
• How many rows on the other side can match one row here?

The first two questions choose the join type. The third tells you how many rows to expect. “Order count per customer” has another grain. First produce the correct detail rows, then aggregate them. DISTINCT cannot repair a lost requirement by hiding orders.

INNER JOIN keeps only real matches

JOIN without INNER means INNER JOIN. A row stays only when both sides satisfy ON:

SELECT c.name, o.id, o.total_cents
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Use it for order detail, books with an author, or any result where a missing right-side row means there is no result row. Customers without orders do not appear because there is no orders row to pair with them.

ON defines the matching rule. Fully qualifying the columns helps too. c.id and o.customer_id show which table each value comes from and avoid ambiguity if a future table adds a column with the same name.

LEFT JOIN keeps the left side, then adds matches

A customer list often needs a different result: customers must appear even before their first order. Put customers on the left:

SELECT c.name, o.id, o.total_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

LEFT JOIN preserves every customers row. When there is no order, right-side fields such as o.id and o.total_cents are NULL. To find customers who have never ordered, add:

WHERE o.id IS NULL

That is not a lookup failure. It is the result of deliberately retaining the left table.

The placement of right-side conditions is an easy trap. If the requirement is “keep every customer, but attach only paid orders,” keep the payment condition in ON:

SELECT c.name, o.id, o.total_cents
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid';

Putting o.status = 'paid' in WHERE removes customers with no order because their right-side value is NULL. The query looks like a left join but again keeps only customers with a matching order.

FULL OUTER JOIN and CROSS JOIN have specific jobs

FULL OUTER JOIN keeps every row on both sides. It fits two import lists: emails in both sources, only source A, and only source B can all remain for comparison.

CROSS JOIN deliberately creates every combination. Two meeting rooms times seven days makes fourteen slots. It fits a size-by-color or date-by-room grid. In an ordinary relational query, using it by accident multiplies rows.

RIGHT JOIN mirrors LEFT JOIN. It works, but most queries become easier to read when the table order is swapped and the query uses LEFT JOIN instead.

A short JOIN review

When reviewing a JOIN, check these points:

1. Which side holds the foreign key, and is this one-to-one, one-to-many, or many-to-many?
2. What does one result row represent?
3. Which side must remain when there is no match?
4. Does each condition define a match, or filter the final result?

Those questions turn “why are there so many rows?” into something testable. They are better than trying DISTINCT until a result looks right.

What I learned

• I can define what one result row means before deciding whether several rows from a one-to-many relationship are expected.
• INNER JOIN keeps matched data. LEFT JOIN keeps the left-side data and fills missing right-side values with NULL.
• ON defines a match, while WHERE filters the result after matching. Moving a right-side condition can make a left join discard rows it was meant to retain.
• FULL OUTER JOIN helps reconcile two sources, while CROSS JOIN intentionally creates every combination. Neither is a general duplicate-row fix.

Conclusion

JOIN syntax is short. The meaning of each result row takes more care. Draw the relationship, choose the result grain, and decide whether unmatched data stays. The SQL then narrows to the join that fits, even when more tables arrive later.

External references

• PostgreSQL: Joins Between Tables
• PostgreSQL: Table Expressions
• Reddit: JOIN doubled my revenue, not in a good way

Further learning

• CS50 SQL Week 1: Relating is Harvard CS50’s official video lesson on relationships, keys, cardinality, and joins.
• PGExercises: Joins and Subqueries provides small datasets for predicting what each row means and checking the result.