How to Use the Postgres EXISTS Operator
Introduction
When you’re performing a PostgreSQL query, there may be times when you want to test for the existence of certain records in a table. To accomplish this task, you can include a subquery in your SELECT statement that makes use of the EXISTS operator. If the subquery returns one or more records, the EXISTS operator will return a value of true; otherwise, it will return false. In this article, we’ll discuss the Postgres EXISTS operator and its opposite, the NOT EXISTS operator. We’ll show you some examples to demonstrate how both of these operators are used.
Prerequisites
Before we proceed with this tutorial, let’s review a couple of key prerequisites that need to be in place:
Make sure you have PostgreSQL installed on your machine in order to follow along with the examples presented in this article.
You should have some basic knowledge of PostgreSQL commands.
Postgres EXISTS Syntax
Let’s begin by looking at the basic syntax for this EXISTS operator:
1 2 3 4 | SELECT column_name FROM TABLE_NAME WHERE EXISTS (SELECT column_name FROM TABLE_NAME WHERE condition); |
If the subquery containing the EXISTS operator returns at least one record, the subquery will evaluate to TRUE; if not, it will evaluate to false.
PostgreSQL EXISTS
Next, let’s add two tables to our PostgreSQL database. We’ll be using these tables in our examples. Here’s the statement used to create the first table:
1 2 3 4 5 | CREATE TABLE continent( continent_id INTEGER PRIMARY KEY, continent_name VARCHAR(50), area_km INTEGER ); |
The second table is created with the statement shown below:
1 2 3 4 5 6 7 | CREATE TABLE countries( country_id INTEGER, country_name VARCHAR(50), area_km INTEGER, continent_id INTEGER REFERENCES continent(continent_id), PRIMARY KEY (country_id, continent_id) ); |
We can then insert some records inside these tables:
1 2 3 4 5 6 7 8 | INSERT INTO continent VALUES ('1', 'Asia', '44579000'), ('2', 'Africa', '30370000'), ('3', 'North America', '24709000'), ('4', 'South America', '17840000'), ('5', 'Antarctica', '14000000'), ('6', 'Europe', '10180000'), ('7', 'Australia', '7692024'); |
1 2 3 4 5 6 7 8 | INSERT INTO countries VALUES ('10', 'United States of America', '9826675', '3'), ('20', 'Canada', '9984670', '3'), ('30', 'China', '9596960', '1'), ('40', 'France', '643801', '6'), ('50', 'Germany', '357022', '6'), ('60', 'United Kingdom', '243610', '6'), ('70', 'South Africa', '1219090', '2'); |
The Postgres EXISTS Examples
Let’s take a look at the following SELECT statement. This query returns rows from the continent table where the continent_id exists in the countries table.
1 2 | SELECT * FROM continent WHERE EXISTS (SELECT * FROM countries WHERE countries.continent_id = continent.continent_id); |
This query will return a result that looks like the following:
1 2 3 4 5 6 7 | continent_id | continent_name | area_km --------------+----------------+---------- 1 | Asia | 44579000 2 | Africa | 30370000 3 | North America | 24709000 6 | Europe | 10180000 (4 rows) |
Now we’ll look at a slightly different example. The following statement will return rows from the countries table where the area of the country found in the continent table is less than 20 million square kilometers:
1 2 3 4 5 6 | SELECT * FROM countries WHERE EXISTS ( SELECT * FROM continent WHERE countries.continent_id = continent.continent_id AND area_km < 20000000 ); |
We should get results that look like this:
1 2 3 4 5 6 | country_id | country_name | area_km | continent_id ------------+----------------+---------+-------------- 40 | France | 643801 | 6 50 | Germany | 357022 | 6 60 | United Kingdom | 243610 | 6 (3 rows) |
The NOT EXISTS Operator in Postgres
The NOT EXISTS operator can be defined as the opposite of the EXISTS operator. It will evaluate to true if the subquery returns no rows; otherwise, it evaluates to true.
The following statement will return rows from the countries table where the area of the continent is not less than 20 million square kilometers:
1 2 3 4 5 6 | SELECT * FROM countries WHERE NOT EXISTS ( SELECT * FROM continent WHERE countries.continent_id = continent.continent_id AND area_km < 20000000 ); |
1 2 3 4 5 6 7 | country_id | country_name | area_km | continent_id ------------+--------------------------+---------+-------------- 10 | United States OF America | 9826675 | 3 20 | Canada | 9984670 | 3 30 | China | 9596960 | 1 70 | South Africa | 1219090 | 2 (4 ROWS) |
Conclusion
When you need to test for the existence of certain records in a PostgreSQL table, the Postgres EXISTS operator is the tool you need to get the job done. In this article, we examined both the EXISTS and NOT EXISTS operators and checked out some examples of their use. With our explanations and examples, you’ll be prepared to utilize these operators in your own PostgreSQL queries.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started