SQL Joins, Groups and NULL

1INNER JOIN and LEFT JOIN

A join asks two tables to share a row when a condition holds. INNER JOIN Sale with Item on item_id keeps a pair only when both sides match. Sale 100 pairs with Samosa; Sale 101 pairs with Tea. Every kiosk sale has an item, so INNER JOIN Sale–Item returns all four sales, now carrying item_name and price_rs.

LEFT OUTER JOIN keeps every row of the left table even when the right side has no match, and fills the right columns with NULL. If we LEFT JOIN Student to Sale, a student who has not bought yet would still appear, with empty sale columns. INNER JOIN would drop that student. Use LEFT when "keep everyone on this side" is the question.

Figure. INNER JOIN matches sale 100 to item 10. The result row can now say "2 Samosa".

Who bought what

1. Match keySale.item_id = Item.item_id.
2. INNERKeep only matching pairs — four sales, each with a name and a price.
3. LEFT from StudentKeep every student; NULL-pad sale columns if they have not bought.

Who bought what

SELECT sale.sale_id, student.name, item.item_name, sale.qty
FROM sale
INNER JOIN student ON sale.student_id = student.student_id
INNER JOIN item ON sale.item_id = item.item_id;

Coding lab. Join Sale to Item and print Asha's lines runs in the app, with checks on your output.

2GROUP BY and aggregates

An aggregate is a number computed from several rows: COUNT, SUM, AVG, MIN, MAX. GROUP BY says "compute that number once per group". GROUP BY student_id on Sale makes three groups: Asha (two sales), Ravi (one), Meera (one). COUNT(*) per group is 2, 1, 1.

Without GROUP BY, an aggregate covers the whole table: SUM(qty) on Sale is 2+1+1+3 = 7 items sold. You cannot SELECT name and SUM(qty) together unless name is in the GROUP BY (or is itself an aggregate). The engine needs to know which name goes with which pile.

Figure. GROUP BY student_id piles the four sales into three groups. Aggregates then run once per pile.

Items sold per student

1. GroupPile Sale rows by student_id: {1}, {2}, {3}.
2. CountAsha 2 rows, Ravi 1, Meera 1.
3. Sum qtyAsha 2+1=3 items; Ravi 1; Meera 3.

Items each student bought

SELECT student_id, COUNT(*) AS sales, SUM(qty) AS items
FROM sale
GROUP BY student_id;

3WHERE versus HAVING

WHERE tests a row before groups exist. HAVING tests a group after aggregates exist. "Sales of tea" is a row test: WHERE item_id = 11. "Students with at least two sales" is a group test: GROUP BY student_id HAVING COUNT(*) >= 2 — that keeps Asha and drops Ravi and Meera.

COUNT(*) >= 2 cannot live in WHERE, because a single sale row does not have a count yet. Put row filters in WHERE (they shrink the piles before you count) and put aggregate filters in HAVING.

Figure. WHERE cannot see COUNT(*). HAVING runs after GROUP BY and can.

Students with two or more sales

1. WHEREOptional row filter first — we keep all four sales here.
2. GROUP BYPile by student_id.
3. HAVINGKeep groups whose COUNT(*) >= 2 — only Asha.

Repeat buyers

SELECT student_id, COUNT(*) AS n
FROM sale
GROUP BY student_id
HAVING COUNT(*) >= 2;
-- Asha only: student_id 1, n = 2

4NULL is unknown, not zero

NULL means "we do not have this value". It is not 0 and not the empty string. Suppose Meera's phone is missing. The comparison phone = NULL is not TRUE and not FALSE — it is UNKNOWN. WHERE phone = NULL therefore drops the row. The test that keeps missing phones is WHERE phone IS NULL.

The same three-valued logic makes NULL = NULL UNKNOWN. Two missing phones are not "equal" in SQL; they are two unknowns. Use IS NULL / IS NOT NULL, never = NULL.

NULL = NULL is UNKNOWN. The IS NULL test is the one that keeps Meera when her phone is missing.

Why = NULL drops the row

1. NULL means unknownMeera's phone is not a value we can compare.
2. = yields UNKNOWNUNKNOWN is not TRUE, so WHERE rejects the row.
3. IS NULLThe dedicated test for "this mark is missing".

Find the missing phone

SELECT name FROM student
WHERE phone IS NULL;
-- not: WHERE phone = NULL

5COUNT(*) versus COUNT(column)

COUNT(*) counts rows. COUNT(phone) counts non-NULL values of phone. If Student has three rows and Meera's phone is NULL, COUNT(*) is 3 and COUNT(phone) is 2. Every other aggregate (SUM, AVG, MIN, MAX) also skips NULLs. Only COUNT(*) treats a NULL row as a row that is still there.

That is why "how many students?" is COUNT(*) and "how many phones do we have on file?" is COUNT(phone). Mixing them is the usual trap on a column that allows missing values.

Figure. Three student rows exist. Only two phones are on file. COUNT(*) sees rows; COUNT(phone) sees values.

Three students, one missing phone

1. RowsAsha, Ravi, Meera — COUNT(*) = 3.
2. Phones on fileAsha and Ravi — COUNT(phone) = 2.
3. Skip ruleSUM/AVG/MIN/MAX skip NULLs the same way COUNT(col) does.

6DISTINCT, UNION and UNION ALL

SQL SELECT keeps duplicate rows. \pi_{\text{item\_id}}(\text{Sale}) in algebra would be {10, 11, 12}. SELECT item_id FROM sale returns 10, 11, 12, 11 — Tea twice. DISTINCT asks SQL to drop the extra 11.

UNION of two queries also drops duplicates (set union). UNION ALL concatenates and keeps them. Use UNION ALL when you already know the piles are disjoint and you do not want the extra distinct pass — for example lunch sales stacked on snack sales that cannot share a sale_id.

SELECT item_id from the four sales lists 11 twice. DISTINCT or UNION (not UNION ALL) drop the extra Tea.