Compare two SELECT statements and follow how moving the status condition changes the expected rows for the supplied students and tasks. Variant A keeps student 1/task 11 and NULL rows for students 2 and 3. Variant B returns only student 1/task 11. These are JavaScript model calculations of expected SQL semantics, not a database run or physical execution plan.
Make the source rows explicit
The fictional students table has one id column with rows 1, 2 and 3. The tasks table has:
| id | student_id | status |
|---|---|---|
| 11 | 1 | done |
| 12 | 1 | todo |
| 13 | 2 | todo |
Student 1 has one done and one todo task. Student 2 has only todo; student 3 has no task. These three cases make filter placement visible without adding real learner information.
CodersTool's visualizer explanation separates table aliases, join conditions, filters and projected columns. The comparison below follows those clause relationships for two fixed queries.
Variant A: limit the right-side match in ON
SELECT s.id AS student_id, t.id AS task_id
FROM students AS s
LEFT JOIN tasks AS t
ON t.student_id = s.id AND t.status = 'done'
ORDER BY s.id, t.id;
PostgreSQL's table-expression documentation explains that LEFT JOIN preserves an unmatched left row with NULLs on the right. It also contrasts restrictions in ON with restrictions in WHERE.
In variant A, a qualifying pair needs both the student-ID match and done status. Student 1 matches task 11. Task 12 and student 2's task 13 do not qualify. Student 2 therefore has no qualifying right-side row, just like student 3, who has no task at all. Both students remain in the result with NULL task IDs.
| student_id | task_id |
|---|---|
| 1 | 11 |
| 2 | NULL |
| 3 | NULL |
NULL represents the absent right-side value. It is not a task with ID zero or an empty string.
Variant B: filter joined rows in WHERE
SELECT s.id AS student_id, t.id AS task_id
FROM students AS s
LEFT JOIN tasks AS t
ON t.student_id = s.id
WHERE t.status = 'done'
ORDER BY s.id, t.id;
For the logical explanation, the ID-only join produces pairs (1, 11), (1, 12), (2, 13), and (3, NULL). WHERE retains only rows whose status test is true. The two todo rows fail; the NULL-extended row does not make the equality true.
The expected projection therefore contains only (1, 11), one row instead of three. WHERE does not invent a replacement NULL row for student 2 after rejecting its todo row.
This difference belongs to these supplied predicates and data. It is not a claim that every WHERE clause removes all unmatched rows or that moving predicates is always harmless.
Map the clauses without inventing an execution plan
| Clause record | Both variants | Placement difference |
|---|---|---|
| Source alias | students → s | None |
| Joined alias | tasks → t, LEFT JOIN | None |
| Key condition | t.student_id = s.id | ON in both |
| Status condition | t.status = 'done' | ON in A; WHERE in B |
| Projection | s.id → student_id; t.id → task_id | None |
| Output order | s.id, then t.id | None |
Keep the clause map beside the expected result rows. Its logical ordering does not describe a database optimizer’s physical execution plan.
No EXPLAIN, index, timing or optimizer evidence is supplied. A diagram cannot establish that one query is faster, and expected rows are not executed results.
Inspect only the reviewed statements
Choose A or B to update the SQL, condition map and expected rows together. Editing the statement clears the old map. Inspect supported query accepts only the two reviewed statements with normalized whitespace; unsupported text gets no recycled result. Reset returns to A. Run your own query against representative data in the intended database before relying on its report. Whitespace differences are allowed, but other SQL changes require a separate database check.
Copy a prompt for this example
Build one self-contained HTML SQL comparison inspector with embedded CSS and JavaScript. Place the source data, editable SQL, condition map, and expected result table in clearly labeled panels that stack on a phone.
Use students with IDs 1, 2, and 3. Use tasks with columns id, student_id, and status: (11, 1, done), (12, 1, todo), and (13, 2, todo).
Provide variant A:
SELECT s.id AS student_id, t.id AS task_id FROM students AS s LEFT JOIN tasks AS t ON t.student_id = s.id AND t.status = 'done' ORDER BY s.id, t.id;
Provide variant B:
SELECT s.id AS student_id, t.id AS task_id FROM students AS s LEFT JOIN tasks AS t ON t.student_id = s.id WHERE t.status = 'done' ORDER BY s.id, t.id;
Compute the expected model rows from the sample data in JavaScript. A returns (1, 11), (2, NULL), and (3, NULL). B returns only (1, 11). Explain how ON limits qualifying matches while WHERE keeps only joined rows whose status comparison is true.
Add A and B buttons, Inspect supported query, and Reset. Accept only these two statements after whitespace normalization. Editing must clear the old map and results; unsupported text must produce an explanation without a recycled result.
Show source aliases, join key, status-condition location, projected columns, and ordering. Label the output as a logical model, not database execution, an optimizer plan, or performance evidence. Return only the complete HTML file.
For a query-review discussion, NOS can display this prepared HTML clause map. Switch to B, trace why students 2 and 3 disappear, and keep any optimizer or performance question for a separate database investigation.