Given the table R with the following data: Column A Column B Column C ---------- ----------...
MathWorks technical mcq question, verified with a worked answer. Free to practise - no sign-up.
Given the table R with the following data:
| Column A | Column B | Column C |
|---|---|---|
| 8 | 5 | 6 |
| 4 | 5 | 6 |
| 3 | 4 | 5 |
| 7 | 2 | 9 |
What is the number of records returned by the following SQL query?
SELECT *
FROM R
WHERE C = ALL(SELECT B FROM R WHERE A > 10)
Show answer & explanation
The subquery SELECT B FROM R WHERE A > 10 returns an empty set because no row has A > 10 (max A is 8). The ALL operator with an empty set causes the WHERE clause C = ALL(...) to evaluate to TRUE for all rows in standard SQL semantics (vacuous truth). However, this is a trick question testing understanding of edge cases: most SQL implementations (MySQL, PostgreSQL, SQL Server) treat C = ALL(empty set) as UNKNOWN/NULL, filtering out all rows. Therefore, 0 records are returned.
Step-by-step Derivation:
Step 1: Evaluate subquery SELECT B FROM R WHERE A > 10. Table R has A values: 8, 4, 3, 7. None exceed 10, so subquery result = empty set {}. Step 2: Evaluate WHERE C = ALL({}) for each row. Step 3: C = ALL(empty set) evaluates to UNKNOWN/NULL in most SQL systems (because there is no value to compare against). Step 4: WHERE clause filters out UNKNOWN results. Step 5: Result = 0 records returned.