SQL Writing Subqueries 3 — Questions and Answers
Question 1: What does NOT EXISTS return when its subquery yields no rows?
- TRUE (Correct answer)
- FALSE
- NULL
- An error
Correct answer: TRUE
NOT EXISTS is TRUE precisely when the subquery returns no rows.
Question 2: Which comparison returns rows greater than EVERY value the subquery returns?
- > ALL (Correct answer)
- > ANY
- > SOME
- > IN
Correct answer: > ALL
> ALL is true only when the value exceeds every value returned by the subquery.
Question 3: A NULL in a NOT IN subquery list can cause what problem?
- The whole predicate may return no rows unexpectedly (Correct answer)
- It speeds up the query
- It is automatically ignored
- It forces a syntax error
Correct answer: The whole predicate may return no rows unexpectedly
NOT IN with a NULL in the list can yield UNKNOWN, filtering out rows you expected to keep.
Question 4: Which is generally preferred for performance when checking existence of related rows?
- EXISTS (Correct answer)
- COUNT(*) > 0 in a scalar subquery
- Loading all rows then filtering
- Using SELECT *
Correct answer: EXISTS
EXISTS can stop at the first matching row, often outperforming counting approaches.
Question 5: In SELECT salary, (SELECT AVG(salary) FROM emp) AS avg_sal, the inner query is a what?
- Scalar subquery in the SELECT list (Correct answer)
- Correlated subquery
- Derived table
- Common table expression
Correct answer: Scalar subquery in the SELECT list
It returns one value placed in the SELECT list, making it a scalar subquery.
Question 6: What must a subquery in the FROM clause always have?
- An alias (Correct answer)
- An ORDER BY
- A GROUP BY
- A WHERE clause
Correct answer: An alias
Derived tables in FROM must be named with an alias in most SQL dialects.
Question 7: Which keyword can replace IN for multi-row equality matching?
- = ANY (Correct answer)
- = ALL
- = ONLY
- = EACH
Correct answer: = ANY
= ANY is logically equivalent to IN, matching any value in the result set.
SQL Writing Subqueries 3
What does NOT EXISTS return when its subquery yields no rows?