SQL EXISTS Operator Explained with Practical Examples

You need the rows of one table that have a matching row in another, and you want the first table's rows back exactly once. EXISTS answers that. It takes a subquery, returns true when the subquery produced at least one row, and false when it produced none, and the row of the outer query is kept or dropped on that answer.

What the SQL EXISTS operator returns

The operator goes in the WHERE clause and takes a subquery in parentheses:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

The PostgreSQL 17 manual describes the test as "The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is true; if the subquery returns no rows, the result of EXISTS is false". Nothing else about the subquery matters, and the same page says so: "the output list of the subquery is normally unimportant". Writing SELECT 1 instead of SELECT * changes nothing and says to the next reader that the columns are beside the point.

Two tables carry every example below. Four students, and four course enrolments between them:

CREATE TABLE students (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(20),
    age          INT
);

CREATE TABLE courses (
    course_id   INT PRIMARY KEY,
    student_id  INT,
    course_name VARCHAR(20)
);

INSERT INTO students VALUES
    (1, 'Alice',   21),
    (2, 'Bob',     22),
    (3, 'Charlie', 23),
    (4, 'Dana',    20);

INSERT INTO courses VALUES
    (1, 1, 'Math'),
    (2, 2, 'History'),
    (3, 3, 'Science'),
    (4, 1, 'History');

Alice is enrolled twice, Dana not at all. Listing the students who are enrolled in something takes one EXISTS:

SELECT student_name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM courses c
    WHERE c.student_id = s.student_id
);

The subquery mentions s.student_id, a column of the outer query, so it asks a different question for each student and the answer changes from row to row. That reference is what makes the query useful, and the next section is what happens without it.

EXISTS with a subquery that ignores the outer row

A subquery that names no column of the outer query gives the same answer on every row, so EXISTS keeps all of them or none of them:

SELECT student_name
FROM students
WHERE EXISTS (SELECT * FROM students WHERE age > 21);

Alice is 21 and Dana is 20, and both are in the result anyway. The subquery only asks whether anybody in the table is over 21, Bob and Charlie answer that once and for all, and every outer row then passes the test. A subquery like this one is a filter on the table as a whole rather than on the row in front of it.

The fix is the join condition the first example carried: repeat the outer column inside the subquery, so that each row is tested against its own matches. NOT EXISTS follows the same rule, and an uncorrelated NOT EXISTS is the version that silently returns everything or nothing.

SQL NOT EXISTS

NOT EXISTS keeps the outer row when the subquery produced no rows at all, which is how you ask for what is missing:

SELECT student_name
FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM courses c
    WHERE c.student_id = s.student_id
);

Dana is the only student with no row in the enrolment table. The same shape narrows to one course by adding a condition inside the subquery, and the students who never took Math come back:

SELECT student_name
FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM courses c
    WHERE c.student_id = s.student_id
      AND c.course_name = 'Math'
);

Alice drops out because her Math row satisfies the subquery. Note where the course condition sits: inside the subquery, next to the correlation. Moving it to the outer WHERE clause would ask a different question, because the outer query has no course_name column to test.

SQL EXISTS against SQL IN

Both operators answer a yes-or-no question about other rows, and for a straightforward match either one works.

The negated forms are where that null becomes visible. PostgreSQL 17 spells it out: "if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true". A NULL anywhere in the subquery's column therefore empties a NOT IN result, while NOT EXISTS asks whether a row came back, which has no third answer. SQL NULL values shows that pair of queries side by side.

Write IN when the right-hand side is a short list of literals you typed yourself, because it reads as what it is. Write EXISTS when the right-hand side is a subquery over a table, and NOT EXISTS whenever the column that subquery selects can hold NULL.

EXISTS with joins

A join and an EXISTS answer different questions, and the difference shows the moment a student has two enrolments:

SELECT s.student_name, c.course_name
FROM students s
INNER JOIN courses c ON c.student_id = s.student_id;

Four rows for three students, because a join returns one row per matching pair. The EXISTS version in the first section returned Alice once, since EXISTS stops at the first match and never multiplies the outer row. Join when you need columns from the second table in the result. Use EXISTS when you need only the first table's rows and the second table is a condition, which also lets you drop the SELECT DISTINCT that was there to undo the join.

EXISTS in a DELETE and an UPDATE

WHERE takes an EXISTS in a DELETE and an UPDATE exactly as it does in a SELECT, and an uncorrelated subquery does far more damage here than in a query. Both statements below run against the rows as first declared.

DELETE FROM students
WHERE EXISTS (SELECT * FROM students WHERE age > 22);

Charlie is over 22, so the subquery returns a row, so the condition is true for every student and the table is left empty. Nothing in the statement says students, ages or Charlie: it says delete everything, on a condition that happens to hold. Correlating it turns it back into the statement you meant, and only the student with no enrolment goes:

DELETE FROM students
WHERE NOT EXISTS (
    SELECT 1
    FROM courses
    WHERE courses.student_id = students.student_id
);

SELECT student_id, student_name, age FROM students;

An UPDATE carries the correlation the same way. Adding a year to every enrolled student leaves Dana alone:

UPDATE students
SET age = age + 1
WHERE EXISTS (
    SELECT 1
    FROM courses
    WHERE courses.student_id = students.student_id
);

SELECT student_id, student_name, age FROM students;

Run the SELECT with the same WHERE clause before you run either statement, and the rows it lists are the rows the statement will change.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Build an EXISTS subquery in DbSchema without typing it

The correlation is the part that goes wrong, and DbSchema's Query Builder writes it for you from the foreign keys. Click a table header in the DbSchema diagram to open the builder loaded with that table, click the small arrow next to a column to follow a foreign key and add the related table, then click the join type label on the connecting line and switch it from INNER JOIN to EXISTS. The generated SQL updates live at the bottom of the builder, so you can read the subquery it produced and tick the columns you want in the SELECT list.

Where a schema has no foreign key between the two tables, drag one column onto the other in the DbSchema diagram to create a virtual foreign key. Virtual foreign keys are saved in the model file rather than in the database, and the Query Builder follows them exactly as it follows real ones. Running the query is the step that reaches the database: paste it into the DbSchema SQL Editor and click Execute Query, or run the SELECT form of a DELETE first and read the rows it lists.

Common mistakes

The first mistake is the uncorrelated subquery from the second section, and it is the one to check for first, because the query runs, returns rows, and looks right until somebody counts them. Read every EXISTS subquery for a reference to the outer query, and if there is none, the operator is filtering the whole table rather than the row.

The second is reaching for EXISTS where IN would read better, or the reverse. EXISTS wants a subquery, so a handful of values you typed yourself belongs in an IN list. IN wants values, so a question about whether related rows are there at all belongs in an EXISTS. The third is putting the condition that narrows the subquery in the outer WHERE clause, where the column it names does not exist and the statement is rejected.

Practice questions

1. List the students enrolled in History.
2. List the courses whose student_id matches no row in the students table.
3. Add a year to the age of every student who has not taken Science.
4. List the students enrolled in both Math and History, using two EXISTS conditions.

Read the rows before you change them, every time. Download DbSchema at https://dbschema.com/download.html, connect to your database, and run the SELECT form of your EXISTS condition in the SQL Editor before you put it in an UPDATE. The connection, the diagram and the SQL Editor are in the free Community Edition; the Query Builder that draws the subquery from your foreign keys is in Pro.

Frequently asked questions

Can EXISTS be used without a subquery?

EXISTS takes a subquery and nothing else, because the question it asks is whether that subquery returned a row.

Is EXISTS faster than IN?

PostgreSQL 17 documents the part you can count on: "the subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion". Which of the two is quicker on your data depends on the plan your engine picks for it, so run both and compare rather than choosing on reputation.