SQL Quiz: Exists Vs In
10 questions · exam conditions
0:00
Exists Vs InQuestion 1 of 10

The Employees table contains department IDs 10, 20, and NULL. The Departments table contains department IDs 10 and NULL.

Query 1: SELECT department_id FROM Employees WHERE department_id NOT IN (SELECT department_id FROM Departments);

Query 2: SELECT e.department_id FROM Employees e WHERE NOT EXISTS (SELECT 1 FROM Departments d WHERE d.department_id = e.department_id);

Which result comparison is correct?

Query 1 returns 20; Query 2 returns 20 and NULL.
Query 1 returns no rows; Query 2 returns 20 and NULL.
Query 1 returns no rows; Query 2 returns only 20.
Query 1 and Query 2 both return 20 and NULL.
← Back to quizzes

SQL Quiz

SQL Quiz: Exists Vs In

Practice Exists Vs In in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Exists Vs In, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

The Employees table contains department IDs 10, 20, and NULL. The Departments table contains department IDs 10 and NULL.

Query 1: SELECT department_id FROM Employees WHERE department_id NOT IN (SELECT department_id FROM Departments);

Query 2: SELECT e.department_id FROM Employees e WHERE NOT EXISTS (SELECT 1 FROM Departments d WHERE d.department_id = e.department_id);

Which result comparison is correct?

  1. Query 1 returns 20; Query 2 returns 20 and NULL.
  2. Query 1 returns no rows; Query 2 returns 20 and NULL. (correct answer)
  3. Query 1 returns no rows; Query 2 returns only 20.
  4. Query 1 and Query 2 both return 20 and NULL.
Explanation: Whenever you see NOT IN versus NOT EXISTS in SQL, the critical concept to keep in mind is how each handles NULL values — this is one of the most common traps on SQL exams. Why B is correct: The Departments table contains NULL as a department ID. When Query 1 evaluates NOT IN (10, NULL), SQL uses three-valued logic: comparing any value to NULL produces UNKNOWN, not TRUE or FALSE. So 20 NOT IN (10, NULL) evaluates as UNKNOWN, and NULL NOT IN (10, NULL) also evaluates as UNKNOWN. Since neither is definitively TRUE, Query 1 returns no rows at all — a classic NULL trap. Query 2 uses NOT EXISTS, which checks row-by-row whether a matching row exists. For department_id = 20, no row in Departments matches, so NOT EXISTS is TRUE → row returned. For department_id = NULL, the join condition d.department_id = NULL is always UNKNOWN, so no match is found, and NOT EXISTS is again TRUE → row returned. Query 2 returns both 20 and NULL. Why the wrong answers fail: A incorrectly assumes NOT IN handles NULL safely and returns 20 — it doesn't. C correctly identifies that Query 1 returns nothing but wrongly claims Query 2 omits NULL; in fact, NOT EXISTS does return the NULL row because the existence check simply finds no match. D assumes both queries behave identically, ignoring the fundamental NULL-handling difference between them. Study tip: Memorize this rule — if a subquery used with NOT IN can return NULL, the outer query returns no rows. Always prefer NOT EXISTS when NULLs may be present.

Question 2

A developer writes the following query to find customers who have orders:

SELECT c.customer_id FROM Customers c WHERE EXISTS (SELECT COUNT(*) FROM Orders o WHERE o.customer_id = c.customer_id);

The intended IN version would be:

SELECT c.customer_id FROM Customers c WHERE c.customer_id IN (SELECT o.customer_id FROM Orders o);

Why can the two queries return different results?

  1. COUNT(*) returns one aggregate row even when no orders match, making EXISTS true. (correct answer)
  2. COUNT(*) returns NULL when no orders match, making EXISTS unknown.
  3. IN requires each customer to have exactly one order, while EXISTS permits several.
  4. IN ignores all duplicate customer IDs, so customers with repeated orders are excluded.
Explanation: When working with EXISTS vs IN, the critical thing to understand is what EXISTS actually checks: it evaluates whether a subquery returns any rows at all, not whether those rows contain meaningful data. Here's the trap in the first query. The subquery SELECT COUNT(*) FROM Orders o WHERE o.customer_id = c.customer_id uses an aggregate function. COUNT(*) always returns exactly one row — even when zero orders match a customer, it returns a single row containing the value 0. Because EXISTS only asks "did the subquery produce at least one row?", it sees that single 0 row and evaluates to TRUE. This means every customer passes the EXISTS check, regardless of whether they have any orders. Answer A correctly identifies this behavior. Answer B is wrong because COUNT(*) never returns NULL — it returns 0 when no rows match. NULL behavior is a concern with COUNT(column_name), not COUNT(*), and even then it wouldn't cause EXISTS to be "unknown." Answer C is wrong because neither IN nor EXISTS has any rule about how many matching rows a customer must have. Both work correctly with zero, one, or many matches when used properly. Answer D is wrong because IN does not exclude customers with duplicate IDs in the subquery. Duplicates in the subquery result are harmless — IN simply checks for membership. Study tip: Whenever you see EXISTS wrapping an aggregate subquery, immediately ask yourself whether that aggregate always produces a row. Aggregates like COUNT(*) silently break EXISTS logic — a common exam trap worth memorizing.

Question 3

Query 1: SELECT c.customer_id FROM Customers c WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.status = 'OPEN');

Query 2: SELECT c.customer_id FROM Customers c WHERE c.customer_id IN (SELECT o.customer_id FROM Orders o WHERE o.status = 'OPEN');

Assume at least one open order exists, but some customers have no open orders.

How do the query results differ?

  1. Query 1 returns every customer; Query 2 returns customers having an open order. (correct answer)
  2. Query 1 returns customers having an open order; Query 2 returns every customer.
  3. Both queries return every customer because the inner result is nonempty.
  4. Both queries return only customers having at least one open order.
Explanation: Whenever you see EXISTS versus IN with a subquery, ask yourself: does the subquery reference the outer query's row, or is it completely independent? In Query 1, the subquery SELECT 1 FROM Orders o WHERE o.status = 'OPEN' has no reference to the outer table — it doesn't say o.customer_id = c.customer_id. This makes it an uncorrelated subquery. SQL evaluates it once: "Does at least one open order exist anywhere?" The problem tells you yes, so EXISTS returns TRUE for every row in Customers. Every customer is returned, regardless of whether they personally have an open order. In Query 2, the subquery SELECT o.customer_id FROM Orders o WHERE o.status = 'OPEN' returns a specific list of customer IDs that have open orders. The IN clause then filters the outer query to only customers whose customer_id appears in that list — so only customers with an open order are returned. This confirms that A is correct: Query 1 returns every customer; Query 2 returns only customers with an open order. B is a simple reversal of the correct behavior — it swaps which query does what. C is a common trap: while both subqueries are nonempty, IN still filters by matching values, not just existence, so it won't return every customer. D would only be true if Query 1 were correlated (e.g., WHERE o.customer_id = c.customer_id), which it isn't. Study tip: Always check whether an EXISTS subquery references the outer table. If it doesn't, it's a constant true/false — a subtle but exam-favorite gotcha.

Question 4

The Customers table contains customer IDs 1, 2, and NULL. The Orders table contains customer IDs 2 and NULL.

Query 1: SELECT customer_id FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders);

Query 2: SELECT c.customer_id FROM Customers c WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id);

Which statement correctly compares the results of Query 1 and Query 2?

  1. Both queries return only customer ID 2. (correct answer)
  2. Both queries return customer ID 2 and NULL.
  3. Query 1 returns 2 and NULL; Query 2 returns only 2.
  4. Query 1 returns only 2; Query 2 returns 2 and NULL.
Explanation: When comparing IN and EXISTS in SQL, the critical concept to understand is how each operator handles NULL values — this is one of the trickiest behavioral differences in SQL. With IN, SQL evaluates each value against the subquery's result set using equality comparisons. When NULL appears in the subquery results, comparing any value to NULL using = yields UNKNOWN (not TRUE or FALSE) due to SQL's three-valued logic. So even though NULL exists in both tables, NULL IN (2, NULL) evaluates to UNKNOWN, not TRUE — meaning NULL from Customers is excluded. Customer ID 1 also fails the match, leaving only 2. With EXISTS, SQL checks whether the correlated subquery returns any rows. For customer NULL in Customers, the condition o.customer_id = c.customer_id becomes NULL = NULL, which again evaluates to UNKNOWN, so no rows are returned — EXISTS evaluates to FALSE and NULL is excluded. Customer 1 similarly finds no match. Only customer 2 satisfies the join condition with a definitive TRUE, so EXISTS also returns only 2. This makes A correct — both queries return only customer ID 2. B is wrong because it assumes NULL = NULL is TRUE, which it isn't in SQL. C is wrong because it incorrectly assumes IN handles NULL differently by including it. D reverses the misconception, wrongly crediting EXISTS with returning NULL. As a study tip, remember: NULL is never equal to anything, including itself. Whenever you see NULL in filter conditions or subqueries, ask yourself what three-valued logic produces — it almost always means exclusion.

Question 5

A developer wants to replace this predicate:

e.department_id NOT IN (SELECT d.department_id FROM Departments d WHERE d.closed_flag = 'Y')

with:

NOT EXISTS (SELECT 1 FROM Departments d WHERE d.closed_flag = 'Y' AND d.department_id = e.department_id)

Which condition is sufficient to make the two predicates equivalent for filtering employee rows under ordinary equality semantics?

  1. Only Departments.department_id is guaranteed to be unique.
  2. Only Employees.department_id is guaranteed to be non-NULL.
  3. The closed-department subquery is guaranteed to return distinct IDs.
  4. Both department ID expressions are guaranteed to be non-NULL. (correct answer)
Explanation: Whenever you compare NOT IN with NOT EXISTS, the critical concept to keep in mind is how SQL handles NULL values — because the two constructs treat NULL very differently, and that difference determines whether they're logically equivalent. NOT IN uses three-valued logic. If the subquery returns even one NULL value, the entire NOT IN predicate evaluates to UNKNOWN (not TRUE) for every outer row, effectively filtering out all rows — a notoriously silent, destructive bug. NOT EXISTS, by contrast, simply checks whether a matching row exists and is immune to this NULL problem. So to make the two predicates behave identically, you must eliminate the scenario where either side of the join comparison is NULL. That means both Departments.department_id (the subquery's returned column) and Employees.department_id (the outer column being tested) must be guaranteed non-NULL. This is exactly what answer D states, making it the correct choice. Answer A is wrong because uniqueness of Departments.department_id is irrelevant — duplicates in the subquery don't affect the logical equivalence of NOT IN vs. NOT EXISTS; only NULL values do. Answer B is a partial truth that fails: even if Employees.department_id is non-NULL, a NULL in the subquery's result set still poisons NOT IN, so you haven't solved the problem. Answer C is also insufficient — distinct IDs still don't prevent a NULL from appearing in those distinct values, so the NOT IN trap remains. As a study tip, always ask yourself: "Can either side of this comparison be NULL?" That single question will catch the most common NOT IN pitfall on SQL exams.

Question 6

Consider these predicates, each used for the same customer row:

Predicate 1: EXISTS (SELECT NULL FROM Orders o WHERE o.customer_id = c.customer_id)

Predicate 2: c.customer_id IN (SELECT o.customer_id FROM Orders o WHERE o.customer_id = c.customer_id)

For a non-NULL customer ID, which statement best explains the role of the values projected by the subqueries?

  1. Predicate 1 is always false because its projected value is NULL.
  2. Predicate 1 depends only on row existence; Predicate 2 compares projected values. (correct answer)
  3. Both predicates compare the outer ID with the literal projected NULL.
  4. Predicate 2 depends only on row existence; Predicate 1 compares projected values.
Explanation: When comparing EXISTS and IN subqueries, the key question to ask is: what does SQL actually use from the subquery's result? The answer differs fundamentally between these two constructs. With EXISTS, SQL only checks whether the subquery returns any rows at all — it completely ignores what those rows contain. That's why SELECT NULL, SELECT 1, or SELECT customer_id are all equivalent inside an EXISTS. The projected value is irrelevant; row existence is everything. With IN, SQL takes the projected column values and checks whether the outer value appears among them. The actual data in the selected column matters for the comparison. This makes B correct: Predicate 1 (EXISTS) evaluates to true the moment a matching row is found, regardless of the NULL being projected. Predicate 2 (IN) succeeds because the subquery returns the actual customer_id values, which SQL then compares against the outer c.customer_id. A is wrong because EXISTS doesn't evaluate the projected value at all — returning NULL has zero effect on whether EXISTS is true or false. C is wrong on both counts: neither predicate compares the outer ID against a literal NULL. Predicate 1 ignores the projection entirely, and Predicate 2 projects real ID values, not NULL. D has the logic exactly backwards — it's EXISTS (Predicate 1) that depends on row existence, not IN (Predicate 2). As a study tip: whenever you see EXISTS, remind yourself it's a row detector, not a value comparator. You can always write SELECT NULL inside EXISTS as a best-practice signal that the projection is intentionally meaningless.

Question 7

The subquery SELECT value FROM T returns two rows: 1 and NULL. Consider this expression:

CASE WHEN 3 IN (SELECT value FROM T) THEN 'Y' WHEN EXISTS (SELECT 1 FROM T WHERE value = 3) THEN 'E' ELSE 'N' END

What does the expression return?

  1. 'Y', because the IN subquery returns at least one row.
  2. 'E', because the NULL row satisfies the existence test.
  3. An error, because IN cannot compare a value with a NULL row.
  4. 'N', because the IN test is unknown and EXISTS is false. (correct answer)
Explanation: When SQL evaluates IN against a subquery containing NULL, three-valued logic (TRUE, FALSE, UNKNOWN) becomes critical. The subquery returns {1, NULL}. Asking whether 3 IN (1, NULL) checks: is 3 = 1? No. Is 3 = NULL? That comparison yields UNKNOWN — not TRUE and not FALSE. Since no row produces TRUE, the entire IN condition resolves to UNKNOWN, not FALSE. A WHEN clause only fires when its condition is TRUE, so the first branch is skipped. SQL then evaluates EXISTS (SELECT 1 FROM T WHERE value = 3). The inner query filters for rows where value = 3. Since the table contains only 1 and NULL, no row satisfies value = 3 — the NULL row fails because NULL = 3 is UNKNOWN, not TRUE, so it is excluded by the WHERE clause. EXISTS returns FALSE. The second branch is also skipped, and the expression falls through to ELSE 'N'. So D is correct. Choice A is wrong because IN returning "at least one row" isn't the criterion — the specific value 3 must match a non-NULL row, and it doesn't. Choice B is wrong because NULL does not satisfy WHERE value = 3; NULL comparisons never evaluate to TRUE, so EXISTS sees an empty result. Choice C is wrong because SQL does not throw an error when IN encounters NULL; it simply produces an UNKNOWN result through three-valued logic. Remember: NULL in an IN list doesn't cause an error or a match — it silently creates UNKNOWN, which can trap an IN test between TRUE and FALSE with no way to resolve.

Question 8

Two logically equivalent queries are available for finding customers with orders. One uses a correlated EXISTS; the other uses IN with a subquery returning customer IDs. A developer claims that EXISTS must always be faster because it can stop after the first match.

Which assessment of the developer's claim is most accurate?

  1. The claim is always correct because IN must materialize and sort every subquery row.
  2. The claim is always incorrect because IN is evaluated only once for the entire statement.
  3. The claim is not generally valid because optimizers may transform both forms into similar plans. (correct answer)
  4. The claim is valid whenever the inner query returns duplicate customer IDs.
Explanation: When comparing EXISTS and IN for performance, the critical concept to understand is the role of the query optimizer. Modern database engines (PostgreSQL, MySQL, SQL Server, Oracle) don't execute your SQL literally — they transform it into an execution plan, and two logically equivalent queries often compile down to the same plan. The developer's intuition has a kernel of truth: a correlated EXISTS can short-circuit after finding the first matching row, which sounds faster. But this reasoning ignores how optimizers work. A mature optimizer recognizes that EXISTS and IN express the same logical relationship and may rewrite both into a semi-join internally. When that happens, the execution plan — and therefore the performance — is essentially identical, making the claim "always faster" invalid. That's why C is correct: the claim is not generally valid because optimizers frequently transform both forms into equivalent plans. A is wrong because IN with a subquery doesn't necessarily materialize and sort every row — optimizers can avoid that overhead entirely by converting the subquery to a join or semi-join. B is wrong in its reasoning: IN is not simply evaluated once like a static list; a subquery inside IN can still be correlated or re-evaluated depending on context, and regardless, "evaluated once" wouldn't make EXISTS slower. D is wrong because duplicates in the subquery result affect correctness considerations in some contexts, not the relative speed advantage of EXISTS over IN. As a study tip: whenever a question claims one SQL construct is always faster than another, be skeptical — optimizer behavior is database-specific and plan-dependent, making absolute performance claims almost always false.

Question 9

An employee may have several rows in EmployeeSkills, including duplicate rows for the same employee_id and skill_code.

Query 1: SELECT e.employee_id FROM Employees e WHERE e.employee_id IN (SELECT es.employee_id FROM EmployeeSkills es WHERE es.skill_code = 'SQL');

Query 2: SELECT e.employee_id FROM Employees e WHERE EXISTS (SELECT 1 FROM EmployeeSkills es WHERE es.employee_id = e.employee_id AND es.skill_code = 'SQL');

Assuming ordinary WHERE semantics, what effect do duplicate qualifying rows in EmployeeSkills have on these queries?

  1. Duplicates repeat employees in Query 1 but not in Query 2.
  2. Duplicates repeat employees in Query 2 but not in Query 1.
  3. Duplicates do not repeat outer employee rows in either query. (correct answer)
  4. Duplicates repeat outer employee rows in both queries unless DISTINCT is added.
Explanation: When a subquery feeds into IN or EXISTS, you need to ask: does the outer query care how many rows the subquery returns, or only whether any qualifying row exists? For IN, the outer query checks whether the outer value appears anywhere in the subquery's result set. Even if EmployeeSkills contains ten duplicate rows for the same employee_id with skill_code = 'SQL', the IN check resolves to a single true/false per outer row. The outer Employees table drives the result — each employee row appears once, regardless of how many inner matches exist. Query 1 returns each employee at most once (assuming employee_id is unique in Employees). For EXISTS, the behavior is identical in this respect. The database evaluates the correlated subquery and stops the moment it finds any matching row — it doesn't count or collect them. One match or ten matches produces the same boolean result, so the outer employee row still appears exactly once. This confirms C is correct: duplicates in EmployeeSkills do not repeat outer employee rows in either query. A is wrong because IN does not multiply outer rows based on inner duplicates — it's a set membership check, not a join. B is wrong for the same reason applied to EXISTS; the subquery is a probe, not a multiplier. D is wrong because DISTINCT is unnecessary here — neither query produces duplicate outer rows due to inner duplicates. A useful rule: outer-row repetition in SQL comes from JOIN, not from IN or EXISTS. If you see duplicates in the inner table, only a JOIN without deduplication will propagate them outward.

Question 10

A query must return employees whose department is active:

SELECT e.employee_id FROM Employees e WHERE e.department_id IN (SELECT d.department_id FROM Departments d WHERE d.active_flag = 'Y');

Which replacement most directly preserves the membership logic by using EXISTS?

  1. WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.active_flag = 'Y')
  2. WHERE EXISTS (SELECT e.department_id FROM Departments d WHERE d.active_flag = 'Y')
  3. WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.department_id = e.department_id AND d.active_flag = 'Y') (correct answer)
  4. WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.department_id <> e.department_id AND d.active_flag = 'Y')
Explanation: When converting IN with a subquery to EXISTS, the critical concept is correlated subqueries — the inner query must reference the outer query's row to test membership for that specific row. Think of IN as asking "is this value in this list?" and EXISTS as asking "does a matching row exist?" The correlation is what makes them equivalent. The original query checks whether each employee's department_id appears in the set of active department IDs. To replicate this with EXISTS, the subquery must link back to the outer employee row — specifically by joining on d.department_id = e.department_id while also filtering d.active_flag = 'Y'. That's exactly what C does: for each employee, it asks "does an active department row exist that matches this employee's department?" The SELECT 1 is just a placeholder — EXISTS only cares whether any row is returned, not what's selected. A is wrong because there's no correlation to the employee's department — it returns true if any active department exists, which would include every employee regardless of their actual department assignment. B makes the same mistake; selecting e.department_id instead of 1 doesn't add the necessary WHERE join condition, so it's still uncorrelated and equally broken. D uses <> instead of =, which inverts the membership logic entirely — it would match employees whose department is not equal to some active department, which is almost always true and meaningless. Your study tip: whenever you rewrite IN (subquery) as EXISTS, always verify the correlated condition outer_table.key = inner_table.key appears in the EXISTS subquery's WHERE clause.