Yogesh Chauhan's Blog

EXISTS and NOT EXISTS in Postgres

in Postgres on July 16, 2020

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.

Syntax:


EXISTS (subquery)

Detailed Syntax:


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.

Examples

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


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
All possible ways you can Insert Data in PostgresPostgresHow to define visibility for a property in PHP?PHP3 Ways we can create URLSearchParams Objects in JavaScriptJavaScriptWindow setTimeout() Method in JavaScriptJavaScriptHow to verify your domain in Google Console with a TXT record?MiscellaneousLearn to Establish Connection using MySQLi (object-oriented), MySQLi (procedural) and PDO with Example CodePHP