As the name suggests, the EXISTS operator is used to test for the existence of any record in a subquery.
It returns true if the subquery returns one or more records.
SELECT column(s) FROM table WHERE EXISTS (SELECT column FROM table WHERE condition);
The argument of EXISTS is an arbitrary SELECT statement, or subquery.
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”.
The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.
The subquery will generally only be executed far enough to determine whether at least one row is returned, not all the way to completion. It is unwise to write a subquery that has any side effects (such as calling sequence functions); whether the side effects occur or not may be difficult to predict.
Since the result depends only on whether any rows are returned, and not on the contents of those rows, the output list of the subquery is normally uninteresting or doesn’t matter.
A common coding convention is to write all EXISTS tests in the form EXISTS(SELECT 1 WHERE …). There are exceptions to this rule however, such as subqueries that use INTERSECT.
Postgres EXISTS Operator Example
I am using the database for all examples. It is available on my Github public repo
SELECT contact_name FROM suppliers WHERE EXISTS (SELECT product_name FROM products WHERE products.supplier_id = suppliers.supplier_id AND unit_price < 20); //Output contact_name "Charlotte Cooper" "Shelley Burke" "Yoshi Nagase" ... ... 24 rows
Let’s divide the whole query into 2 parts.
Part 1: Postgres will run subquery for each and every row and check if it returns TRUE. If so then it will return the name of the supplier otherwise it will skip it.
Part 2: The subquery: We are getting the names of the products of our suppliers (from suppliers table) which has price of 20 or less. We are using correlated subquery so we can’t just use subquery to get the products just in this case.
Postgres NOT EXISTS Operator Example
SELECT contact_name FROM suppliers WHERE NOT EXISTS (SELECT product_name FROM products WHERE products.supplier_id = suppliers.supplier_id AND unit_price < 20); //Output contact_name "Regina Murphy" "Antonio del Valle Saavedra" "Sven Petersen" "Eliane Noz" "Chantal Goulet"
We are getting the opposite result set than previous query.
What if we add NULL to subquery?
Postgres EXISTS Operator with NULL input Example
SELECT contact_name FROM suppliers WHERE EXISTS (SELECT NULL); or SELECT contact_name FROM suppliers; //Same output contact_name "Charlotte Cooper" "Shelley Burke" "Regina Murphy" "Yoshi Nagase" ... ... 29 rows
Credit: Postgres functions subquery