SQL Academy · Lesson

IN vs ANY vs ALL

Compare against sets of values.

IN vs ANY vs ALL is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Are IN, ANY, and ALL?

When writing SQL queries, you often need to compare a value against a set of values. SQL gives you three powerful operators for this: IN, ANY, and ALL.

All three can work with subqueries that return a list of values. They look similar but behave differently — choosing the right one can make your query much more expressive and efficient.

The IN Operator

The IN operator checks whether a value matches any value in a list or subquery result. It is the simplest of the three.

The example below retrieves employees who work in the Sales, HR, or Engineering departments.

SELECT name, department
FROM employees
WHERE department IN ('Sales', 'HR', 'Engineering');

IN With a Subquery

IN becomes especially powerful when paired with a subquery. Instead of a hard-coded list, you can pull the comparison values directly from another table.

Here, we find all employees who belong to a department that has a budget above 100,000.

SELECT name, department
FROM employees
WHERE department IN (
  SELECT dept_name
  FROM departments
  WHERE budget > 100000
);

The ANY Operator

ANY (also written as SOME) compares a value against each value returned by a subquery using a comparison operator such as =, >, <, >=, or <=.

The condition is true if the comparison is true for at least one value in the subquery result.

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE department = 'HR'
);

= ANY Is the Same as IN

An important equivalence to know: = ANY (subquery) is logically identical to IN (subquery). Both return true when the value matches at least one result from the subquery.

These two queries produce the same result:

-- Using IN
SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'NYC');

-- Using = ANY
SELECT name FROM employees
WHERE department_id = ANY (SELECT id FROM departments WHERE location = 'NYC');

The ALL Operator

ALL compares a value against every value in a subquery result. The condition is true only if the comparison holds for all values returned.

This example finds employees whose salary is greater than every salary in the Intern grade — meaning they earn more than the highest-paid intern.

SELECT name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE job_grade = 'Intern'
);

ANY vs ALL — Side by Side

The key difference: ANY needs the condition to be true for at least one value, while ALL needs it to be true for every value.

Think of it like this: ANY is like OR across all comparisons, and ALL is like AND across all comparisons.

-- Returns rows where salary > at least one value in the subquery
SELECT name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Support');

-- Returns rows where salary > every value in the subquery
SELECT name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Support');

NOT IN vs <> ALL

Just as = ANY mirrors IN, the operator <> ALL is equivalent to NOT IN. Both return true only when the value does not match any value in the subquery.

Be careful though — if the subquery returns even one NULL, NOT IN will return no rows at all due to SQL's three-valued logic.

-- Using NOT IN
SELECT name FROM employees
WHERE department_id NOT IN (SELECT id FROM departments WHERE location = 'Remote');

-- Using <> ALL (equivalent)
SELECT name FROM employees
WHERE department_id <> ALL (SELECT id FROM departments WHERE location = 'Remote');

The NULL Trap With NOT IN

One critical gotcha: if the subquery used with NOT IN returns a NULL, the entire NOT IN condition evaluates to UNKNOWN for every row, returning an empty result set.

To avoid this, filter out NULLs from the subquery, or use NOT EXISTS instead — which handles NULLs safely.

-- Dangerous: if manager_id has any NULL, no rows are returned
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

-- Safe: filter NULLs explicitly
SELECT name FROM employees
WHERE id NOT IN (
  SELECT manager_id FROM employees
  WHERE manager_id IS NOT NULL
);

Practical Example: Finding High Earners

Let us see all three operators applied to a realistic scenario. We have an employees table with name, department, and salary columns.

We want employees who earn more than the average salary of any single department — meaning more than at least one department's average.

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT AVG(salary)
  FROM employees
  GROUP BY department
)
ORDER BY salary DESC;

When to Use Which?

Here is a quick guide for choosing between the three operators:

IN — when you have a static list or need simple equality matching against a subquery.
= ANY — same as IN, but lets you use other comparison operators like >, <.
ALL — when the condition must hold against every value (e.g., greater than the maximum without using MAX).
NOT IN — convenient but watch for NULLs; prefer NOT EXISTS when the subquery may contain NULLs.

Quick Check

Test your understanding of IN, ANY, and ALL.

Lesson Recap

In this lesson you learned how to compare values against a set using IN, ANY, and ALL.

Key takeaways:

IN checks membership — true if the value matches any item in the list or subquery.
= ANY is equivalent to IN; using other operators like > ANY means true for at least one match.
ALL means the condition must hold for every value returned by the subquery.
NOT IN silently returns no rows when the subquery contains NULLs — always guard against this.
Use NOT EXISTS as a safer alternative to NOT IN when NULLs may be present.

Frequently asked questions

Is the “IN vs ANY vs ALL” lesson free?

Yes — the full text of “IN vs ANY vs ALL” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “IN vs ANY vs ALL”?

Compare against sets of values. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “IN vs ANY vs ALL” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy