Skip to content
Tutorials Center
My learning

Topics

Lesson 45 of 125 ยท Subqueries and Set Operations

EXISTS

Learn SQL EXISTS and NOT EXISTS for semi-joins and anti-joins, why NOT EXISTS beats NOT IN with NULLs, how to answer "for all" questions, and ANY/ALL in other databases.

Many questions only need a yes or no about related rows. Has this customer ever ordered? Is this employee on any project? Does this course have at least one student? You don't care which rows match, or how many; you only care whether at least one exists.

EXISTS asks exactly that. It takes a subquery and returns true if the subquery produces at least one row, and false if it produces none. Its opposite, NOT EXISTS, is true when the subquery finds nothing. Almost always the subquery is correlated: it refers to the current outer row, so the question is asked again for every row.

EXISTS is one of the most useful tools in SQL. It never duplicates rows, it handles NULL safely, it often runs fast, and with a double NOT EXISTS it can even answer "for all" questions that are hard to express any other way.

EXISTS: at least one matching row

Which customers of the shop database have placed at least one order?

SQL Customers with orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Read it as: "customers for whom there exists an order with their id". Without the LIMIT, the query returns 34 of the 40 customers. Every customer appears once, no matter how many orders they placed. Customer 5, for example, has seven orders but still one row.

Why SELECT 1?

EXISTS only checks whether the subquery returns rows; it never looks at the values in them. SELECT 1, SELECT *, SELECT o.id and even SELECT NULL behave the same:

SQL The selected value doesn't matter ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both count 34. A row whose only column is NULL is still a row. SELECT 1 is the common convention because it tells readers "only existence matters here".

NOT EXISTS: no matching row

Flip it around to find the customers who never ordered:

SQL Customers without orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Six customers, 35 to 40. Queries like this power real features: a reminder email to people who signed up but never bought, a cleanup of products nobody has ever ordered, an alert for projects without staff.

Conditions inside the subquery

The subquery can contain any filter, so EXISTS can ask precise questions. Which projects in the company database have no reviewer assigned? The assignments table has a role column with values like 'Lead', 'Contributor' and 'Reviewer':

SQL Projects without a reviewer ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Two projects come back: the Design System and Enterprise Outreach have staff, but nobody reviewing their work. Notice where the condition a.role = 'Reviewer' goes: inside the subquery. It defines which related rows count. Putting it in the outer WHERE wouldn't even make sense, because the outer query can't see a.

You can combine several existence checks with AND and OR, just like any other condition. Here are employees who lead at least one project but don't review any:

SQL Leads who never review ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Each subquery can reuse the alias a, because each has its own scope.

Semi-joins and anti-joins

Database people have names for these two patterns:

  • A semi-join keeps the rows of one table that have at least one match in another, without adding columns or duplicating rows. EXISTS and IN both express it.
  • An anti-join keeps the rows that have no match. NOT EXISTS, LEFT JOIN ... WHERE ... IS NULL and (carefully) NOT IN express it.

Here are three ways to write the anti-join "employees who manage nobody", run side by side:

SQL Three anti-joins, one trap ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first two agree: 29 employees have no reports. NOT IN returns 0, because the CEO's manager_id is NULL, and x NOT IN (..., NULL) can never be true (the Subqueries lesson explains why). NOT EXISTS doesn't have this problem: it simply asks whether a matching row exists, and report.manager_id = e.id is never true for a NULL, so that row just doesn't match.

Make NOT EXISTS your default anti-join. It's correct with NULLs, it reads like the question ("where no report exists"), it can't create duplicate rows, and every major database optimizes it well. LEFT JOIN ... IS NULL is equally correct and fine when you already have the join in place; use NOT IN only on columns that can't be NULL.

For semi-joins, IN and EXISTS are usually interchangeable and run equally fast on modern databases. EXISTS is more flexible: it can match on several columns at once (WHERE a.x = t.x AND a.y = t.y) and add extra conditions, while IN compares a single value with a single column (some databases allow row values like (x, y) IN (...), including SQLite).

A common mistake: forgetting the correlation

An EXISTS subquery that doesn't refer to the outer row gives the same answer for every row: either all rows pass or none do.

SQL An uncorrelated EXISTS ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The broken query returns all 40 customers, because some cancelled order exists in the shop. The fixed one returns 13: only the customers who have a cancelled order of their own. If an EXISTS query returns every row or no rows, check the correlation first.

There are legitimate uses for an uncorrelated EXISTS, though: "show the report only if there is data", or a guard in an INSERT ... SELECT ... WHERE NOT EXISTS (...) that prevents inserting a duplicate. Here it's the intent that matters.

"For all" questions with double NOT EXISTS

Some questions say "every": students who have taken every computer science course, customers who bought from every category, employees who worked on all of their department's projects. SQL has no FOR ALL keyword, but logic gives you a translation:

"The student took every required course" means "there is no required course that the student did not take."

That's two NOT EXISTS, one inside the other. In the school database, who has taken both introductory and intermediate computer science (CS100 and CS210)?

SQL Students who took every listed course ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Seventeen students have taken both. This pattern is called relational division. Try changing the list to c.code LIKE 'CS%': that adds CS300, a seminar nobody has enrolled in yet, and the result becomes empty, because every student is now missing a required course.

The same question can be answered by counting, which some people find easier to read:

SQL The same answer by counting ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Both return the same 17 students. The counting version needs the number of required courses (here 2) and DISTINCT (a student might repeat a course in another term). The double NOT EXISTS version needs neither, and it gives the logically correct answer when the requirement list is empty: if no courses are required, every student qualifies.

ANY, SOME and ALL in other databases

Standard SQL also has quantified comparisons that compare a value with every row of a subquery. PostgreSQL, MySQL, SQL Server and Oracle support them; SQLite does not, so these are read-only:

SQL
-- PostgreSQL / MySQL / SQL Server / Oracle
-- Engineers who earn more than ALL non-head designers
SELECT first_name, salary
FROM employees
WHERE department_id = 1
  AND salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 2
      AND title NOT LIKE 'Head%'
  );

-- = ANY means the same as IN; SOME is a synonym for ANY
SELECT first_name
FROM employees
WHERE department_id = ANY (SELECT id FROM departments WHERE office = 'Berlin');

Each form has a SQLite equivalent:

Quantified comparison Equivalent you can run in SQLite
x = ANY (subquery) x IN (subquery)
x <> ALL (subquery) x NOT IN (subquery) (same NULL trap) or NOT EXISTS
x > ALL (subquery) x > (SELECT MAX(...)), or NOT EXISTS (... WHERE value >= x)
x > ANY (subquery) x > (SELECT MIN(...)), or EXISTS (... WHERE value < x)
SQL ALL rewritten for SQLite ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Seven engineers earn more than every non-head designer (the best-paid of those, Umar Andersson, earns 75,000). The NOT EXISTS version reads as "there is no such designer earning at least as much".

Edge cases differ. If the subquery returns no rows, x > ALL (...) is true, while x > (SELECT MAX(...)) compares with NULL and is never true. The NOT EXISTS rewrite matches ALL: with no rows to contradict it, the condition holds. When the subquery can contain NULLs, ALL can become unknown; NOT EXISTS simply ignores those rows. Decide which behavior your question needs.

Performance notes

  • EXISTS can stop at the first matching row. It never needs to count or collect all matches, which makes it cheap when an index can find a match quickly.
  • Index the column the subquery correlates on (orders.customer_id, assignments.employee_id). Without an index, each outer row may scan the whole inner table.
  • Modern optimizers usually run EXISTS, IN and the equivalent join with the same plan. When they don't, NOT EXISTS is generally the safest bet for anti-joins.
  • Don't add DISTINCT, ORDER BY or LIMIT 1 inside an EXISTS subquery; they don't change the result and only add noise.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does WHERE EXISTS (SELECT NULL FROM orders AS o WHERE o.customer_id = c.id) do for a customer with two orders?

    EXISTS only checks whether any row comes back; the values (even NULL) don't matter, and it never duplicates outer rows.
  2. 2. A column manager_id contains some NULLs. Which query reliably finds employees who manage nobody?

    NOT EXISTS isn't affected by NULLs in manager_id. NOT IN returns nothing when the list contains a NULL.
  3. 3. An EXISTS query returns every row of the outer table, which seems wrong. What is the most likely mistake?

    Without a correlation like o.customer_id = c.id, the subquery is true for everyone as long as any matching row exists anywhere.
  4. 4. How do you express "students who took every required course" with EXISTS?

    "Took every course" means "there is no required course the student didn't take": a double NOT EXISTS, also called relational division.
  5. 5. Which SQLite query is equivalent to PostgreSQL's WHERE x = ANY (SELECT y FROM t)?

    = ANY is true when x equals at least one value in the list, which is exactly what IN means.

Summary

  • EXISTS (subquery) is true when the subquery returns at least one row; the selected values don't matter, so write SELECT 1.
  • NOT EXISTS is true when there is no matching row; it's the safest anti-join because NULLs can't break it.
  • Put the conditions that define a matching row inside the subquery, and always correlate it with the outer row.
  • EXISTS and IN are semi-joins: they filter without duplicating rows. NOT EXISTS and LEFT JOIN ... IS NULL are anti-joins.
  • "For every" questions become a double NOT EXISTS (relational division), or a GROUP BY ... HAVING COUNT(DISTINCT ...).
  • ANY, SOME and ALL exist in other databases; in SQLite use IN, MIN/MAX or EXISTS instead, minding the empty-set and NULL edge cases.
  • Index the correlated column; EXISTS can stop at the first match.

Next: Common Table Expressions, where you'll name subqueries with WITH and build queries step by step.