Back to all posts

Antijoins, semijoins, and some generalized quantifiers in SQL

For SQL users who write joins and subqueries daily; the set-theory names for these queries are given where they help.

On this page

You want the rows of one table that have no counterpart in another. Standard SQL gives you no keyword for it, though the pattern has a name, the antijoin, and three formulations: a correlated NOT EXISTS, the set operator EXCEPT, and an outer join filtered on the side that came back null. A few dialects do name the operation, Spark SQL among them, where LEFT ANTI JOIN is one of the join types the parser accepts.

The examples run against two tables, the second carrying a foreign key to the first:

CREATE TABLE Customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT,
    email       TEXT,
    country     TEXT
);

CREATE TABLE Orders (
    order_id    INTEGER PRIMARY KEY,
    order_date  DATE,
    customer_id INTEGER REFERENCES Customers
);

INSERT INTO Customers VALUES
    (1, 'Mira',  '[email protected]',  'IE'),
    (2, 'Tomas', '[email protected]', 'PT'),
    (3, 'Ines',  '[email protected]',  'ES'),
    (4, 'Yuki',  '[email protected]',  'JP');

INSERT INTO Orders VALUES
    (101, '2026-03-02', 1),
    (102, '2026-03-05', 1),
    (103, '2026-03-06', 2);

Which customers have never placed an order is the question every query below answers, and Ines and Yuki are the answer. If you want to review the foreign key first, our prior tutorial on foreign keys covers it.

Diagram showing Orders references Customer

Antijoins with NOT EXISTS

The first formulation keeps a customer when no order with that customer's id exists:

SELECT c.customer_id, c.name, c.email, c.country
FROM Customers c
WHERE  NOT EXISTS (
	SELECT 1
	FROM Orders o
	WHERE o.customer_id = c.customer_id  )
customer_idnameemailcountry
3Ines[email protected]ES
4Yuki[email protected]JP

The inner SELECT 1 refers to the alias c of the outer query, which makes it a correlated subquery: it is evaluated for each candidate row of Customers rather than once. SELECT 1 returns the constant 1 for every matching order, and NOT EXISTS cares only whether any row came back at all, so the projection inside the subquery is arbitrary.

EXCEPT and the other set operators

A correlated subquery is not the only approach. If you are comfortable with set theory, EXCEPT reads as the shorter answer, and it is, as long as the customer id is all you want:

SELECT customer_id FROM Customers
EXCEPT
SELECT customer_id FROM Orders;
customer_id
3
4

The result carries no duplicates: the PostgreSQL 18 manual states that "The result of EXCEPT does not contain any duplicate rows unless the ALL option is specified". Oracle spells the operator MINUS, and only from Oracle Database 21c is EXCEPT accepted as well, a synonym for MINUS with the exact same semantics; the Oracle 19c manual lists only UNION, UNION ALL, INTERSECT and MINUS.

The cost arrives as soon as you want a second column. Set operators compare whole rows, so both sides need the same number of columns and compatible types, and Customers and Orders have only customer_id in common. Getting the name and the country of a customer with no orders means wrapping the set operation in another query:

SELECT * FROM Customers WHERE customer_id IN (
    SELECT customer_id FROM Customers
    EXCEPT
    SELECT customer_id FROM Orders
);

Two rows again, Ines and Yuki, with all four columns. Two statements to say what NOT EXISTS said in one is the trade: EXCEPT is the shortest form when the key is the whole answer, and the correlated form is shorter as soon as it is not.

Antijoins using outer joins

The third formulation joins the two tables and then keeps only the rows where the join found nothing. A left outer join emits a row for every customer, filling the Orders columns with nulls where no order matched, and order_id IS NULL is true on exactly those rows:

SELECT c.customer_id, c.name, c.email, c.country
FROM Customers c
	LEFT OUTER JOIN Orders o ON ( o.customer_id = c.customer_id  )
WHERE o.order_id IS NULL
customer_idnameemailcountry
3Ines[email protected]ES
4Yuki[email protected]JP

The null test has to name a column that is never null in Orders, which is why order_id is the one to pick: a nullable column would confuse a genuine null with an unmatched row. The query is shorter than the correlated version while asking more of the reader, who has to recall what an outer join does with unmatched rows before the IS NULL makes sense.

Semijoins and how the joins map onto set operators

The counterpart query, the one that keeps the customers who did order, is a semijoin. In SQL you write EXISTS where the antijoin wrote NOT EXISTS:

SELECT c.customer_id, c.name, c.email, c.country
FROM Customers c
WHERE  EXISTS (
	SELECT 1
	FROM Orders o
	WHERE o.customer_id = c.customer_id  )
customer_idnameemailcountry
1Mira[email protected]IE
2Tomas[email protected]PT

Both names in use for the antijoin, antijoin and anti-semijoin, refer to this same operation; the longer one exists to stress that the antijoin is the complement of the semijoin rather than of some other kind of join.

The semi in both names points at the projection: only the columns of one table survive into the result. That is a second dimension next to the usual one. A join decides which rows come out, and it also decides which attributes.

Take two tables R and S. A holds the rows of R with no match in S, B the rows that match, and C the rows of S with no match in R.

R S A B C
OutputSQL set operators
(match on all attributes )
SQL join operators
(match on some attributes)
AR EXCEPT SR left anti-semijoin S
B (roughly)R INTERSECT SR INNER JOIN S or semijoins
(depends on attributes projected)
CS EXCEPT RR right anti-semijoin S
A, BRR LEFT OUTER JOIN S
B, CSR RIGHT OUTER JOIN S
A, B, CR UNION SR FULL OUTER JOIN S

Inner joins and semijoins share a cell in that table because the rows they return are the same, B, and only the projection separates them. For an antijoin the projection settles itself: the rows come from the table you are subtracting from, and the other table contributed nothing but nulls. For B there are three sensible choices, separated by which table's attributes come out:

Output rowsOutput attributesSQL join operators
BRR left semijoin S
BSR right semijoin S
BR and SR INNER JOIN S

Threshold counts (generalized quantifiers)

Existence and non-existence are the two extremes of a count. The interesting questions often sit between them: the customers with fewer than two orders, say, which the outer-join query answers once it gains a grouping and a threshold.

SELECT c.customer_id, c.name, c.email, c.country, count(DISTINCT o.order_id)
FROM Customers c
	LEFT OUTER JOIN Orders o ON ( o.customer_id = c.customer_id  )
GROUP BY c.customer_id, c.name, c.email, c.country
HAVING count(DISTINCT order_id) < 2;
customer_idnameemailcountrycount(DISTINCT o.order_id)
2Tomas[email protected]PT1
3Ines[email protected]ES0
4Yuki[email protected]JP0

The outer join is what keeps Ines and Yuki in the result: their group holds one row with a null order_id, and count of a null column counts nothing, so the threshold sees 0 rather than 1. An inner join here would answer a different question, the one about customers who ordered exactly once.

In logic, "there exists" is a quantifier, and a constraint of the form "there exist fewer than three" is a generalized quantifier. The name is worth knowing because it groups this query with the antijoin rather than with reporting: the count is a filter on the parent row, not a figure anybody wants to read.

The correlated form of the same question puts the count in a column position:

SELECT  c.customer_id, c.name, c.email, c.country,
        (SELECT COUNT(DISTINCT  o.order_id) FROM Orders o
         WHERE  o.customer_id = c.customer_id) AS order_count
FROM Customers c
WHERE order_count < 2;

That runs on SQLite and is rejected by Oracle, PostgreSQL and SQL Server, which apply the standard scoping rule to order_count. PostgreSQL 18 puts it plainly: an output column's name can be used in ORDER BY and GROUP BY clauses, "but not in the WHERE or HAVING clauses; there you must write out the expression instead". What comes back is a complaint about the name, not about the count:

ERROR:  column "order_count" does not exist

Wrapping the query, minus its WHERE clause, in an outer query that applies the filter is the fix, at the price of a shape nobody enjoys reading:

SELECT * FROM (
  SELECT c.customer_id, c.name, c.email, c.country,
         (SELECT COUNT(DISTINCT o.order_id) FROM Orders o
          WHERE o.customer_id = c.customer_id) AS order_count
  FROM Customers c
) counted
WHERE order_count < 2;

The same three customers come back, with the count as a fifth column. The alias counted on the derived table is there because the SQL standard requires a sub-SELECT in the FROM list to carry one, a rule PostgreSQL relaxes and SQL Server does not.

Of the three antijoin formulations, write NOT EXISTS unless you have a reason not to: it states the condition in the words of the question, and it is the one that survives a second condition, since narrowing the subquery to the orders of the last quarter is a line added inside it rather than a restructured query.

Build these queries in DbSchema

DbSchema's Query Builder draws Customers and Orders with the line that joins them, and writes the SQL as you click. The join starts as an inner join; click the join label on the line to choose Left Join, Full Join, Exists or Not Exists instead.

DbSchema Query Builder with Customers and Orders joined on the diagram

The filtered left join from the outer-join section is one that the Query Builder assembles from the diagram as well:

DbSchema Query Builder writing an antijoin as a filtered left-join

Adding a grouping and a threshold to it gives the query for customers with fewer than two orders:

DbSchema Query Builder writing a generalized quantifier

Of the two count queries, the correlated one, with its subquery in a column position, is the one that the Query Builder does not draw, which costs little: the grouped form is the one it generates, and the correlated variant runs in DbSchema's SQL Editor beside it. Save and Close keeps the query of either editor in the design model, while the queries themselves run against the live database and only read it.

The DbSchema Query Builder is a Pro edition feature; the SQL Editor and the interactive diagrams it sits on come with the free Community edition. Download DbSchema at https://dbschema.com/download.html, reverse-engineer a schema you already have, and write the NOT EXISTS query in the SQL Editor with your own two tables in place of Customers and Orders.