What distinguishes a scalar subquery from a row subquery and a table subquery?
answer
- think in columns and rows, not in clauses
- one of the three can be used as a value
- one of them is compared against a parenthesised list
- column count is known before the data is read
- degree and cardinality name the two dimensions
basics
~20 sThe three forms differ by result shape. A scalar subquery returns one column and at most one row and acts as a value; a row subquery returns one row of several columns and is compared against a row constructor; a table subquery returns any number of rows and columns.
solid answer
~50 sSQL classifies a nested query by its **degree** (columns) and **cardinality** (rows), and each position in a statement demands a particular shape. A **scalar** subquery is one column and at most one row, so it can be used anywhere a value is legal — `WHERE total > (SELECT AVG(total) FROM orders)`. A **row** subquery returns a single row of several columns and is compared against a row constructor, as in `WHERE (city, country) = (SELECT city, country FROM headquarters)`. A **table** subquery has no shape restriction at all and is what `IN`, `EXISTS` and the `FROM` clause accept. Getting the degree wrong is a static error caught when the statement is prepared — PostgreSQL says the subquery has too many or too few columns. Getting the cardinality wrong is a runtime error, because only the data can decide how many rows come back.
code
sql · 8 lines-- scalar: one column, at most one row, used as a value
SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);
-- row: one row of two columns, compared against a row constructor
SELECT * FROM offices WHERE (city, country) = (SELECT city, country FROM headquarters);
-- table: any shape, consumed as a set
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'FR');go deeper
Be able to name the three forms and give one example of each: a value in a comparison, a pair matched at once, and a list used with IN.
Explain degree versus cardinality and use it to predict the error: too many columns is rejected when the statement is prepared, too many rows only when it runs.
Show that you pick the form from the shape of the answer rather than by habit, and can read an engine's shape complaint straight to the fix in review.
Talk about the review convention this implies — that = against a subquery encodes a uniqueness assumption, and assumptions that data can violate belong in constraints, not in query text.
## Two dimensions, three names Every subquery has a result shape described by two numbers: how many columns it projects (its **degree**) and how many rows it produces (its **cardinality**). The traditional names in the SQL standard carve that space into three useful cases. | Form | Degree | Cardinality | Behaves as | |---|---|---|---| | Scalar subquery | exactly 1 | at most 1 | a value | | Row subquery | n ≥ 1 | exactly 1 | a row | | Table subquery | any | any | a table | The form is not declared anywhere; it is inferred from where you wrote the subquery. The surrounding syntax states what shape it needs, and the engine checks the subquery against that requirement. ## Scalar subqueries: the value form Wherever SQL accepts an expression, it accepts a parenthesised single-column, single-row query in its place: ```sql SELECT order_id, total, total - (SELECT AVG(total) FROM orders) AS delta_from_mean FROM orders WHERE total > (SELECT AVG(total) FROM orders); ``` Both uses are scalar. Zero rows makes the expression NULL; more than one row is a cardinality violation raised at execution time. ## Row subqueries: the tuple form A row subquery returns one row with several columns, and is compared against a **row constructor** — a parenthesised list of expressions, optionally spelled `ROW(a, b)`: ```sql SELECT * FROM order_items oi WHERE (oi.order_id, oi.line_no) = ( SELECT order_id, line_no FROM shipment_lines WHERE tracking_code = 'TRK-9001' ); ``` The two sides must have the same degree, and the corresponding columns must be comparable types. This form lets a composite key be matched in one predicate instead of two, which matters because two separate scalar subqueries against the same source could in principle be satisfied by different rows. ## Table subqueries: the set form A table subquery imposes no shape rule. It is what appears after `IN`, inside `EXISTS`, and in the `FROM` clause as a derived table: ```sql SELECT customer_id FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'FR'); ``` Here many rows are the point, not an error. Note that different table-subquery positions still constrain degree differently: the single-column form of `IN` needs one column, `EXISTS` ignores the select list entirely, and `FROM` accepts anything. ## Which errors are static and which are dynamic This is the distinction worth carrying away, because it predicts when you will find out about the mistake. **Degree is static.** The subquery's SELECT list fixes the column count, so the engine knows it while preparing the statement. `WHERE id = (SELECT id, name FROM users)` is rejected before any data is read, with a message about the subquery having too many columns. Likewise `WHERE (a, b) = (SELECT x FROM t)` fails for having too few. **Cardinality is dynamic.** How many rows come back depends entirely on the data present when the statement runs. That is why `WHERE id = (SELECT id FROM users WHERE email = ?)` prepares cleanly, works for years, and fails the day two users share an email address. So shape errors split neatly: the ones about columns are caught by writing the query, and the ones about rows are caught only by running it against unlucky data. ## Choosing the form deliberately In practice the choice follows from the question being asked: - Comparing against **one computed value** — a maximum, an average, a configured threshold — is scalar. - Matching a **composite key or a pair of attributes at once** is a row subquery, where the engine supports row comparison; where it does not, the same intent is written as several ANDed predicates. - Testing **membership** or feeding a table expression is a table subquery. A common code-review smell is a scalar subquery being used where the author actually meant membership: `= (SELECT ...)` against something not guaranteed unique. The form was chosen by habit rather than by the shape of the answer, and the data eventually says so. ## Why the taxonomy is worth knowing by name Error messages speak in these terms. "Subquery has too many columns", "more than one row returned by a subquery used as an expression", "operand should contain 1 column" — each of these is telling you the position expected one shape and the query delivered another. Once you read the message as a shape complaint, the fix is mechanical: change the subquery's shape, or change the position to one that accepts the shape you have.
- Which of these shape mistakes does an engine catch before executing the statement?Only the column-count ones. The subquery's SELECT list fixes its degree, so a mismatch such as `id = (SELECT id, name FROM users)` is rejected at prepare time. Row count depends on the data, so a scalar subquery returning two rows can only be detected once the statement runs.
- If an engine does not support row subqueries, how do you express the same intent?Spell the comparison out as ANDed predicates, one scalar subquery per column. It is more verbose, repeats the inner query, and — unlike a single row comparison — does not by itself guarantee that both values came from the same source row, so it is worth restricting the inner query to a key that identifies one row.
saying these in an interview costs you the question
- Thinks the subquery form is declared rather than inferred from position
- Says a scalar subquery may return several columns
- Believes every subquery must return exactly one row
- Cannot say which shape errors are caught before execution
- Uses = with a subquery where membership was meant