3.1SELECT, WHERE and joins in SQL
The idea
An SQL query is evaluated in a fixed logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Joins are part of FROM.
Think of it like this: An assembly line: first gather the tables, then filter rows, group them, filter groups, and finally choose the columns.
Why it exists
Understanding the order solves most "what does this query return" questions.
How it works
- FROM builds the joined table (a cross product restricted by ON).
- WHERE removes rows.
- GROUP BY / HAVING form and filter groups.
- SELECT picks columns; DISTINCT removes duplicates; ORDER BY sorts last.
Worked example: Emp LEFT JOIN Dept ON did, with Dia's did NULL.
Dia stays with NULL department columns. A WHERE condition on Dept columns would later remove her.
Now play with it
- →Compare the four join types in the lab.
- →Note how many rows the CROSS JOIN gives.
| eid | name | did |
|---|---|---|
| 1 | Asha | 10 |
| 2 | Ben | 20 |
| 3 | Chen | 10 |
| 4 | Dia | NULL |
| did | dname |
|---|---|
| 10 | CSE |
| 20 | ECE |
| 30 | ME |
| eid | name | did | dname |
|---|---|---|---|
| 1 | Asha | 10 | CSE |
| 2 | Ben | 20 | ECE |
| 3 | Chen | 10 | CSE |
Matches on the common column did. Dia (did NULL) and ME (no employee) disappear.
| SQL | Meaning | Rows kept |
|---|---|---|
| A JOIN B ON … | inner join | only matching pairs |
| A LEFT JOIN B ON … | left outer | all of A, matched or NULL-padded |
| A RIGHT JOIN B ON … | right outer | all of B |
| A FULL JOIN B ON … | full outer | all of both |
| A CROSS JOIN B | cartesian product | m × n |
| A NATURAL JOIN B | join on equal common columns, one copy kept | matching pairs |
Order: FROM (joins) → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
Check yourself
In a LEFT JOIN, a WHERE condition on a column of the right table that must equal a value
Exam notes (the dense version)
Logical evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. SELECT keeps duplicates unless DISTINCT. INNER JOIN = matches only; LEFT / RIGHT / FULL OUTER JOIN add unmatched rows with NULLs. A condition on the right table in WHERE after a LEFT JOIN can silently turn it back into an inner join; put it in ON instead.
Previous year questions: SQL (13)
PYQs are grouped by topic, so this is the full set for SQL; try the ones that match this subtopic. Open all 13 on the topic page.