Study guides · Introductory

Reason about SQL rows, joins, and aggregates

Track how filtering, joining, and grouping change a result so that duplicates and NULL values do not silently change its meaning.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Define what identifies a row
  2. A join creates matching pairs
  3. Test the NULL case explicitly
  4. Validate meaning before optimizing
  5. Trace three users through an outer join
  6. Ask Logeia about this articleCheck your understanding

Define what identifies a row

Consider a users table and an orders table. A user_id identifies a user; each order has an order_id and a buyer user_id. A primary key identifies a row, while a foreign key constrains a reference. Similar column names alone do not create a valid relationship or enforce its integrity.

SQL query results can contain duplicate values. Do not assume the word relation means every projected result is automatically deduplicated. Decide which columns identify the result and whether duplicates are meaningful. When an order of presentation matters, specify ORDER BY instead of relying on storage layout or an earlier execution.

A join creates matching pairs

If one user has three orders, joining on user_id can produce three rows containing that user. This is the expected consequence of a one-to-many relationship. Counting joined rows with COUNT(*) is not necessarily counting people, and a faster execution cannot repair that semantic mistake.

Group by the user when an order count per user is required. Use a LEFT JOIN if users without orders must remain. A condition on an order column in WHERE can eliminate the NULL-extended rows, so decide where a condition belongs by considering which unmatched rows the query should retain.

sql
SELECT u.user_id, COUNT(o.order_id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.user_id
GROUP BY u.user_id
ORDER BY u.user_id;

Test the NULL case explicitly

NULL is not an ordinary value that can be tested with equality in the usual way. Use IS NULL to ask whether a column is NULL. WHERE keeps rows for which its condition is true, not rows for which a comparison has an unknown result. An unknown age therefore does not establish that age >= 18.

In this example, COUNT(o.order_id) returns zero for a user with no orders when order_id is non-null in real orders. The aggregate ignores the NULL introduced by the outer join. COUNT(*) instead counts the result row itself. A tiny example with no orders makes the distinction visible before a large report hides it.

Validate meaning before optimizing

Test empty data, one match, multiple matches, NULL values, and duplicate display names. Once the result satisfies the requirement, inspect indexes and plans for performance. Separate whether a query answers the right question from whether it answers quickly.

This explanation describes a logical way to reason about a query, not a promise about the physical execution order chosen by an optimizer. Database products also differ in syntax, type conversion, collation, and constraints. Check the selected product rather than generalizing every PostgreSQL detail to all SQL systems.

Trace three users through an outer join

Take users 1, 2, and 3, with orders 10 and 11 owned by user 1 and order 12 owned by user 2. A left join yields (1,10), (1,11), (2,12), and (3,NULL). Grouping and counting order_id yields (1,2), (2,1), and (3,0). COUNT(*) would count the unmatched output row and report one for user 3.

To count only paid orders while retaining every user, add the order-status condition to ON. Putting that condition in WHERE instead removes the NULL-extended rows. Write down the expected result before optimizing. This row trace explains query meaning, not the physical join algorithm the optimizer must use.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 11

Choose a learning path