The term “predicate” is used to refer to an expression that determines whether something is true or false. Or in other words, it makes an assertion and returns true or false based on that.

The EXISTS predicate accepts a subquery as input and returns true or false, depending on whether the subquery returns a non-empty set or an empty one respectively.

The EXISTS predicate, unlike most predicates in SQL, uses a two-valued logic. It returns either true or false. It cannot return unknown because there is no situation where it doesn’t know whether the subquery returns at last one row or none.

One-to-Many

Here we have a one-to-many relationship between Customers and their Orders. A customer can have zero, one or many orders. Below is some queries exhibiting the EXISTS predicate in Itzik’s database.

-- Customers who placed orders (89 rows)
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE EXISTS (
    SELECT * FROM Sales.Orders AS O
    WHERE O.custid = C.custid);
-- Customers who did NOT place orders (2 rows)
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS (
    SELECT * FROM Sales.Orders AS O
    WHERE O.custid = C.custid);
-- All customers (91 rows)
SELECT * FROM Sales.Customers;