Joins — Which Side Do You Keep

In a nutshell

A join is an operation that pairs up rows of two sets by a condition, and most bugs in practice come from deciding wrongly how to handle rows that have no partner.

Why this was needed

The price of normalization is joins. We split customer information and order information, so to see them together we have to attach them again. The problem is what to do with "rows that have no partner". Do you keep customers who have no orders in the result or leave them out? This decision is precisely the join type.

How it works

INNER JOIN — keeps only rows that have a partner on both sides.
LEFT JOIN — keeps all rows on the left, and fills the right-side columns with NULL when there is no partner.
Anti join — keeps only rows that have no partner. Writing it with NOT EXISTS is the clearest.
CROSS JOIN — every combination. If you leave out the join condition, you unintentionally get this.

Here we must point out the most important trap. If you write a condition on the right table in the WHERE after a LEFT JOIN, it becomes an INNER JOIN at that moment.

-- 모든 고객 + 그중 결제 완료 주문 (고객 400명 유지)
SELECT c.id, count(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id;

-- 결제 완료 주문이 있는 고객만 (고객 수가 줄어든다)
SELECT c.id, count(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id;

Rows with no partner have NULL in the right columns, and o.status = 'paid' returns unknown for NULL, so those rows are filtered out entirely. The rule is to put the filter on the right side of a left outer join in the ON clause, and the filter on the left table in the WHERE clause.

There is one more trap when combined with aggregation. If you use count(*) after a LEFT JOIN, even rows with no partner are counted as 1. You must count a column from the joined side, as in count(o.id), to get 0. This takes advantage of the property that aggregate functions ignore NULL.

What it looks like in the field

The fact that a join multiplies rows is also often forgotten. If an order has three line items, the join result has three rows, and if you then do sum(o.total_amount), the order amount becomes three times as large. This kind of fan-out is a typical cause of silently inflated report numbers. The solution is to aggregate first and then join, or to fold it in advance with a subquery rather than with sum(DISTINCT ...).

In terms of performance, it is worth knowing the three join algorithms. A nested loop is advantageous when there are few outer rows and the inner side has an index, a hash join when it is an equality join and one side can be loaded into memory, and a merge join when both sides are already sorted. The optimizer does the choosing, but if the number of nested loop iterations in the plan exceeds tens of thousands, it is a sign that the estimate of the number of outer rows was wrong.

Summarizing join types in a picture

A (주문)          B (고객)
 ┌───┐            ┌───┐
 │ 1 │────────────│ 1 │      INNER   : 양쪽에 다 있는 것만
 │ 2 │────────────│ 2 │      LEFT    : A 는 전부, 짝 없으면 B 쪽이 NULL
 │ 3 │            │   │      RIGHT   : B 는 전부
 │   │            │ 4 │      FULL    : 양쪽 전부
 └───┘            └───┘

In practice 90% is INNER and LEFT. It is easier to read if you flip RIGHT into LEFT, and FULL is used for data reconciliation (finding what exists on only one side).

Where you write the condition in a LEFT JOIN changes the result. This is the most common mistake.

-- 취소되지 않은 결제가 있는 주문 + 결제가 아예 없는 주문
select o.*, p.amount
from orders o
left join payments p on p.order_id = o.id and p.status <> 'canceled';
                                            ↑ ON 절 — 짝을 찾는 조건

-- 사실상 INNER JOIN 이 된다. 짝이 없는 행은 p.status 가 NULL 이라 걸러진다
from orders o
left join payments p on p.order_id = o.id
where p.status <> 'canceled';
      ↑ WHERE 절 — 조인 결과를 거르는 조건

If you write a condition on the right table in WHERE after a LEFT JOIN, the meaning of LEFT disappears. The rule is to put conditions on the right table in ON and conditions on the left table in WHERE.

Noticing that rows swell

A join multiplies rows when there are several partners. If an order has 3 payments, that order becomes 3 lines, and if you then do sum(o.amount), the amount becomes 3 times.

-- ❌ 주문 금액이 결제 건수만큼 부풀려진다
select sum(o.amount) from orders o join payments p on p.order_id = o.id;

-- ✅ 미리 접어서 조인한다
select sum(o.amount)
from orders o
join (select order_id from payments group by order_id) p on p.order_id = o.id;

-- ✅ 또는 존재 여부만 물을 때는 EXISTS
select sum(o.amount) from orders o
where exists (select 1 from payments p where p.order_id = o.id);

EXISTS stops when it finds the first partner, so it does not multiply rows and is usually faster. When you only need to know whether one exists, this is more appropriate than a join.

The NULL trap of NOT IN

-- 서브쿼리 결과에 NULL 이 하나라도 있으면 전체가 빈 결과가 된다
select * from orders where customer_id not in (select id from vip_customers);

-- 안전하다
select * from orders o
where not exists (select 1 from vip_customers v where v.id = o.customer_id);

x NOT IN (1, 2, NULL) is x <> 1 and x <> 2 and x <> NULL, and the last one is UNKNOWN, so the whole cannot be true. Replacing NOT IN with NOT EXISTS is safer.

What you will do in the next lab

You will write inner joins, outer joins, anti joins, and self joins in turn, and compare directly how the results differ when the same condition is put in the ON clause and in the WHERE clause.