Querydrill

Filtering Updated November 2026

IN

IN tests whether a value is in a list: WHERE country IN ('Aruba', 'Curacao', 'Bonaire') is the same as three OR conditions. The list can also be a subquery that returns one column: WHERE customer_id IN (SELECT id FROM customers WHERE country = 'Aruba'). NOT IN keeps the rows that do not match.

Syntax

WHERE column IN (value1, value2, ...) or WHERE column IN (SELECT column FROM other_table)

Examples

SELECT name FROM customers WHERE country IN ('Aruba', 'Curacao');

Result: Customers from Aruba or Curacao

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'Aruba');

Result: Orders placed by customers in Aruba

The subquery must return a single column.

SELECT name FROM products WHERE id NOT IN (SELECT product_id FROM order_items);

Result: Products that were never sold

Only correct if product_id can never be NULL; otherwise the whole result is empty.

Common mistakes

  • NOT IN (subquery) returns no rows at all when the subquery contains a NULL; add WHERE column IS NOT NULL inside the subquery or use NOT EXISTS.
  • A subquery inside IN that returns two columns raises an error (sub-select returns 2 columns, expected 1); select only the matching column.
  • Writing IN with a single value and no parentheses is a syntax error; IN always needs parentheses.

Practice IN

These drills check your answer on live data, free, no account needed.

  • Two countriesReturn the name and country of customers based in the US or the Netherlands (NL).

Questions

What is the difference between IN and EXISTS?

Both can filter by another table. IN compares one value with a list; EXISTS checks whether a correlated subquery returns any row and handles NULLs safely. For NOT, prefer NOT EXISTS over NOT IN.

Can I use IN with multiple columns?

PostgreSQL and SQLite support row values: WHERE (country, city) IN (SELECT country, city FROM hubs). Otherwise use EXISTS or a join.

Related

  • WHEREUse WHERE to keep only the rows that match a condition: equals, greater than, text matches, NULL checks, and combinations with AND, OR and NOT.
  • EXISTSEXISTS is true when the subquery returns at least one row. Use it for has-any checks and NOT EXISTS for rows with no match, the safe alternative to NOT IN.
  • SubqueryA subquery is a SELECT inside another query. Scalar subqueries (compare to an average), IN subqueries, correlated subqueries and derived tables in FROM.
  • AND, OR, NOTHow to combine WHERE conditions with AND, OR and NOT, why AND binds before OR, how parentheses fix it, and how NULL changes the result of a logical test.

Previous: LIKENext: BETWEEN