The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › SQL

JOIN and GROUP BY

Do you put the condition in ON or in WHERE?

Join 3 members and 4 orders. The join type, the place where you write the condition, and the way you aggregate all change which rows come out. Logically, SQL processes FROM/JOIN → WHERE → GROUP BY → HAVING in this order, and if you follow the rows that are left after each step, you can see why.

Checked against real execution · The SQL for the 18 combinations was run in SQLite and checked against the results

Concepts in this lab

JOIN (INNER, OUTER)

The operation that combines rows of two tables according to a condition. INNER JOIN includes only the rows where the condition matches on both sides. LEFT OUTER JOIN includes every row of the left table and fills in NULL when there is no match on the right. Choose between them based on whether data without a match must stay in the result.

GROUP BY, WHERE, HAVING

GROUP BY groups rows that have the same value in the specified columns and computes an aggregate (such as a sum or a count) for each group. WHERE filters individual rows before they are grouped, and HAVING filters groups after grouping. So put conditions on aggregate results in HAVING and all other conditions in WHERE.

Primary key, unique key, foreign key

A primary key (PK) is the column that uniquely identifies each row of a table. Its values cannot be duplicated or empty, and a table has only one. A unique key is a unique constraint, which prevents duplicate values, and a table can have several. A foreign key (FK) is a column that references the primary key (or a unique key) of another table. It guarantees that the referenced row really exists.

Try these

• Default settings — A LEFT JOIN, but the condition is in WHERE. The member with no orders disappears.
• Moving the same condition to ON — The member with no orders stays, together with NULLs.
• INNER JOIN — Only the rows that have a match on both sides come out.
• LEFT JOIN, no condition — The member with no orders comes out as one row filled with NULL.
• Paid orders per member — With a LEFT JOIN and the condition in ON, a member with 0 orders is counted too.
• Only members with 2 or more — HAVING filters after the rows are grouped.

Running with the default settings

1. Step 1, FROM and JOIN: for each member, find the orders that satisfy the ON condition (same member ID) and attach them. A member with no match is not thrown away: one row is kept, with the order columns filled with NULL. Rows now: 5.
2. Step 2, WHERE: from the joined result, keep only the rows where o.status = 'PAID'. Rows removed: 2. Carol's row has NULL in o.status, so it does not satisfy the condition and disappears too. The reason for keeping her with a LEFT JOIN is gone, and the result becomes the same as with an INNER JOIN.
3. Final result. Rows: 3.

Result of this run

• If you put a WHERE condition on a column of the right table after a LEFT JOIN, and the condition is not true for NULL (for example, only status PAID), the rows filled with NULL are filtered out, and the result becomes the same as with an INNER JOIN. If you want to keep the rows with no match and attach only the orders that satisfy the condition, write the condition in ON.

Code

SELECT m.name, o.id, o.status
  FROM member m
  LEFT JOIN orders o
    ON o.member_id = m.id
 WHERE o.status = 'PAID';

Assumptions of this model

This is a small example with 3 members and 4 orders. The results were calculated by hand, following the logical processing order of standard SQL. A real database may change the order internally (for example, by applying the WHERE condition before the join), but the result must be the same as if it were processed in this order. For all 18 combinations, the SQL shown on the screen was run as is on a real SQL engine (SQLite), and the final rows matched the results of the model.

Previous lab: SELECT processing order · Next lab: Subqueries