Subqueries · SQL SQL
What is the primary operational difference between using the EXISTS operator with a correlated subquery versus using IN with a subquery?
Practice question from Subqueries (SQL SQL), with the correct answer and a step-by-step solution.
hard · MCQ · Q-4CFDFE
What is the primary operational difference between using the EXISTS operator with a correlated subquery versus using IN with a subquery?
- A.EXISTS returns true or false based on whether any rows are returned and typically short-circuits upon finding the first match, whereas IN evaluates the entire list of values.Correct answer
- B.IN allows multi-column comparisons whereas EXISTS only permits single-column comparisons.
- C.EXISTS requires an explicit JOIN clause inside the subquery, while IN does not.
- D.IN automatically removes duplicates, whereas EXISTS retains all duplicate rows from the underlying table.
Solution
EXISTS evaluates a boolean condition checking for row existence and can short-circuit (stop searching) as soon as the first matching row is found. IN evaluates the expression against a set of values, which conceptually involves materializing or checking the entire result set, though modern optimizers may rewrite them similarly.
More from Subqueries
- Which of the following best describes a correlated subquery?
- Consider the query: SELECT FROM employees WHERE departmentid IN (SELECT departmentid FROM departments WHERE location = 'New York'); Which r…
- Explain the concept of a derived table (also known as an inline view) and state where it must be placed in a SQL query.
- Which of the following queries correctly uses a scalar subquery in the SELECT list to retrieve each employee's salary alongside the company…
Practise more SQL
Sign up free for adaptive practice on this chapter, with instant feedback and hints on every question.
Start practising freeFree to start · No credit card needed