SQL: bags, NULL logic and joins
Goals and execution model
Predict SQL query results from small tables, including duplicate multiplicities, missing values and unmatched joins. Use the standard core illustrated by PostgreSQL documentation; no vendor-specific optimizer plan is assumed. Logical evaluation is FROM/JOIN, WHERE, then SELECT and DISTINCT, with ORDER BY determining presentation order. This is a reasoning model, not a promise that the physical engine executes operators in that sequence.
1. SQL results are usually bags
For T(x) with rows 2,2,5, SELECT x FROM T returns three rows, while SELECT DISTINCT x FROM T returns 2 and 5 once each. Without ORDER BY, their order is unspecified. UNION removes duplicates; UNION ALL preserves their combined multiplicities. If query L produces 2,2 and query R produces 2,5, UNION gives {2,5}; UNION ALL gives a bag with three 2s and one 5. Set operations require compatible column counts and types.
Join multiplicities multiply. If a particular join key appears twice on the left and three times on the right, an equality join produces six matching pairs for that key, assuming all six comparisons are true. DISTINCT after projection may collapse them, but the join itself does not choose only one partner.
2. NULL is a marker, not an ordinary value
Ordinary comparisons such as x=5, x<>5 and x=NULL evaluate to UNKNOWN when an operand is NULL. In particular NULL=NULL is UNKNOWN under ordinary equality. Use IS NULL or IS NOT NULL to test the marker. WHERE keeps only TRUE; both FALSE and UNKNOWN are rejected. A NULL value is not automatically zero or an empty string.
The central three-valued identities are: NOT UNKNOWN=UNKNOWN; FALSE AND UNKNOWN=FALSE; TRUE AND UNKNOWN=UNKNOWN; TRUE OR UNKNOWN=TRUE; FALSE OR UNKNOWN=UNKNOWN. Consequently p OR NOT p is UNKNOWN when p is UNKNOWN, so it is not a universal row-retaining filter.
Worked trace: T(x) has 2,NULL,5. WHERE x<>2 returns only 5. WHERE NOT(x=2) also returns only 5. WHERE x<>2 OR x IS NULL returns NULL and 5. WHERE x=NULL returns no rows. These statements distinguish “known to differ” from “different or unknown.” DISTINCT groups duplicate NULL markers together for duplicate elimination; it does not use ordinary NULL=NULL to decide that operation.
3. Inner joins and explicit predicates
Let Student(sid,name) contain (1,Ana),(2,Bob),(3,Chen), and Enrol(sid,cid) contain (1,A),(1,B),(2,A). All ids are non-null. SELECT s.sid,e.cid FROM Student s JOIN Enrol e ON s.sid=e.sid gives (1,A),(1,B),(2,A). Student 3 has no match. Matching requires ON to be TRUE. If nullable join keys are both NULL, ordinary equality does not match them.
Use aliases to disambiguate columns. JOIN ... ON can compare differently named columns or express non-equality predicates. NATURAL JOIN implicitly matches every shared column name; adding a new shared name can change the result. Explicit ON conditions make the intended relationship clearer.
4. Outer joins and predicate placement
A LEFT JOIN preserves each left row at least once. A left row with matches yields its matching pairs; one with no true match yields a null-extended right side. For the same tables, SELECT s.sid,e.cid FROM Student s LEFT JOIN Enrol e ON s.sid=e.sid returns (1,A),(1,B),(2,A),(3,NULL).
Now compare two queries:
Q1: SELECT s.sid,e.cid FROM Student s LEFT JOIN Enrol e ON s.sid=e.sid AND e.cid='B'; Q2: SELECT s.sid,e.cid FROM Student s LEFT JOIN Enrol e ON s.sid=e.sid WHERE e.cid='B';
Q1 returns (1,B),(2,NULL),(3,NULL). Bob has no B match and is preserved; Chen has no match at all and is preserved. Q2 returns only (1,B): Bob's A row fails WHERE and Chen's null-extended row makes e.cid='B' UNKNOWN. ON decides matches before preservation; WHERE filters the resulting rows. Moving predicates across an outer join is therefore not generally safe.
RIGHT JOIN is the mirrored preservation of the right table; FULL JOIN preserves unmatched rows from both sides. Their match pairs still obey the same multiplicity and truth rules.
5. Anti-join pattern and its assumption
To find students with no enrollment, use LEFT JOIN followed by WHERE e.sid IS NULL when e.sid is guaranteed non-null for any genuine matching row. The result is student 3. Test a non-null right-side key, not a nullable attribute such as grade: a real enrollment can have grade NULL and would be incorrectly treated as unmatched. NOT EXISTS provides another direct pattern, developed in the next lesson.
Pitfalls
- Plain SELECT does not remove duplicates, and no ORDER BY means no guaranteed order.
- UNKNOWN is not TRUE; WHERE does not retain it.
- NULL tests use IS NULL, not =NULL.
- LEFT JOIN can return more rows than its left input when multiple matches exist.
- Filtering a right-side column in WHERE can eliminate null-extended rows.
Original practice: six single-correct MCQs
These original teaching questions are not previous-year questions. Choose exactly one answer.
P01. T(x) contains 7,7,NULL. How many rows does SELECT x FROM T return?
A. 1 B. 2 C. 3 D. 0
Answer: C
A: Plain SELECT does not collapse all rows.
B: DISTINCT would collapse the duplicate 7.
C: All three input rows remain without filtering or DISTINCT.
D: NULL does not remove its row from plain SELECT.
P02. What is the result of ordinary SQL NULL=NULL?
A. TRUE B. FALSE C. UNKNOWN D. Zero
Answer: C
A: Ordinary equality cannot establish this with missing values.
B: It is not known false either.
C: A NULL operand makes ordinary equality UNKNOWN.
D: A truth value is not the numeric default zero.
P03. Which predicate selects NULL markers in x?
A. x=NULL B. x<>NULL C. x IS NULL D. NOT(x=NULL)
Answer: C
A: This evaluates to UNKNOWN.
B: This also evaluates to UNKNOWN.
C: IS NULL directly tests the marker.
D: NOT UNKNOWN remains UNKNOWN.
P04. A join key appears 2 times in L and 4 times in R. All equality matches are true. Matching pairs for this key?
A. 2 B. 4 C. 6 D. 8
Answer: D
A: Each left occurrence has four partners.
B: There are two left occurrences, not one.
C: Adding multiplicities is not joining.
D: 2×4=8 pairs.
P05. After a LEFT JOIN, WHERE right_col=5 sees a null-extended row. What happens to that row?
A. Always retained B. Rejected because the predicate is UNKNOWN C. Replaced by zero D. Duplicated
Answer: B
A: WHERE retains only TRUE.
B: NULL=5 is UNKNOWN, so WHERE removes the row.
C: The predicate does not replace values.
D: A filter does not duplicate rows.
P06. Which expression is TRUE in SQL three-valued logic?
A. NOT UNKNOWN B. FALSE OR UNKNOWN C. TRUE AND UNKNOWN D. TRUE OR UNKNOWN
Answer: D
A: NOT UNKNOWN is UNKNOWN.
B: FALSE OR UNKNOWN is UNKNOWN.
C: TRUE AND UNKNOWN is UNKNOWN.
D: One true disjunct makes OR true.
Sources and scope
Aligned to the 2026 course. This lesson covers the stated slice, not the entire Databases syllabus. All numerical examples and questions are original. No claim of 2027 completeness.
Sign in to keep your progress. Sign in