sql

SQL EXISTS Operator

1. Introduction

The SQL EXISTS operator is a logical operator that is used to check whether a subquery returns any row. It returns TRUE if the subquery returns one or more rows, else it returns FALSE.

SQL EXISTS operator syntax:

WHERE EXISTS ( subquery )

In this example, I will demonstrate the following use cases:

2. Technologies Used

The example code in this article was built and run using:

  • SQL Server
  • SQL

3. Setup Data

In this step, I will create two tables: products and categories and insert some sample data.

3.1 Categories

In this step, a categories table is created. It has two columns: category_id and category_name.

create table categories

create table categories (
category_id INT,
category_name VARCHAR(40)
);

Insert three records into the categories table.

insert categories

insert into categories ( category_id, category_name ) values(1, 'Mobile');
insert into categories ( category_id, category_name ) values(2, 'Headphone');
insert into categories ( category_id, category_name ) values(3, 'Tablet');

3.2 Products

Create a products table that has a category_id column linking to the categories table.

create table products

create table products (
product_id INT,
category_id INT,
product_name VARCHAR(40),
price	DECIMAL(5,2),
release_date DATE
);

Insert records into the products table.

insert products

insert into products ( product_id , category_id, product_name, price, release_date ) values(1027,2, 'Headphone 700',87.99, '5/13/2019');
insert into products ( product_id , category_id, product_name, price, release_date ) values(1028,2, 'Headphone 450BT', 97.99, '2/4/2020');
insert into products ( product_id , category_id, product_name, price, release_date ) values(1029,2, 'HD 1000XM3', 107.99, '8/5/2018');
insert into products ( product_id , category_id, product_name, price, release_date ) values(1030,2, 'HD SoundES18',117.99, '1/1/2017');
insert into products ( product_id , category_id, product_name, price, release_date ) values(1021,1, 'APPLE 700',400.99, '9/20/2019');
insert into products ( product_id , category_id, product_name, price, release_date ) values(1022,1, 'Samsung 5',357.99, '8/23/2019');
insert into products ( product_id , category_id, product_name, price, release_date ) values(1035,3, 'GalatS6',187.99, '10/13/2019');
insert into products ( product_id , category_id, product_name, price, release_date ) values(1036,3, 'MS', 250.99, '6/13/2017');
insert into products ( product_id , category_id, product_name, price, release_date ) values(1036,33, 'Apple Watch', 850.99, '6/13/2020');

Note: the last insert statement has the category_id of 33 which is not in the categories table. It won’t return when using a Join clause.

4. Exists Examples

4.1 Subquery Always Returns a Row

The “select null” subquery always returns TRUE, so you will see that “mary” is returned.

subquery 1: select null

select 'mary' as Mary
where exists (select null)

The select ‘mary’ as mary query prints “mary” for both column and value.

Query results

Mary
mary
Table 1 Select ‘mary’ as Mary

The subquery with an aggregation function always returns a row. The following query will return all the rows in the products table even there aren’t any products that have a price higher than 3000.

subquery with an aggregation function

select * from products
where exists ( select count(*) from products where price > 3000);

Query Results

product_idcategory_idproduct_namepricerelease_date
10272MicroService87.992019-05-13
10282Headphone 450BT97.992020-02-04
10292HD 1000XM3107.992018-08-05
10302HD SoundES18117.992017-01-01
10211APPLE 700400.992019-09-20
10221Samsung 5357.992019-08-23
10353GalatS6187.992019-10-13
10363MS250.992017-06-13
10373Dell280.992017-07-13
103633Apple Watch850.992020-06-13
Table 2 All 10 Records In Products

This query returns all the records in the products table. So be careful when using an aggregation function with the EXISTS operator.

4.2 SELECT Statement

The products table does not have any records with a price higher than 3000, so the following query returns no data.

Subquery finds no data

select * from products
where exists ( select * from products where price > 3000);

If we change the subquery by comparing it to 300, then it returns all the products as Table 2 because the subquery finds records.

subquery finds data

select * from products
where exists ( select * from products where price > 300);

Note: pay attention to the requirements when using the Exists operator.

4.3 INSERT Statement

Insert a record into the products table only if there is no such product exists.

Insert Not Exists

insert into products (product_id, category_id, product_name, price, release_date) 
select 1036,3, 'MS', 250.99, '6/13/2017'
where NOT exists (select product_id from products where product_name = 'MS');

It shows “0 Row(s) Inserted” since there is a record that exists already with product_name=’MS’.

Note: using Not Exists in an insert statement is a good practice to avoid duplicate data.

5. Exists vs In

The Exists operator provides a similar function with the In clause. It should use the In clause when comparing to a list of items.

in ( subquery)

select * from products p 
where p.category_id in ( select category_id from categories c );

In Results

product_idcategory_idproduct_namepricerelease_date
10272MicroService87.992019-05-13
10282Headphone 450BT97.992020-02-04
10292HD 1000XM3107.992018-08-05
10302HD SoundES18117.992017-01-01
10211APPLE 700400.992019-09-20
10221Samsung 5357.992019-08-23
10353GalatS6187.992019-10-13
10363MS250.992017-06-13
10373Dell280.992017-07-13
Table 3 In Operator

We can use the Exists operator to achieve the same results.

exists (subquery)

select * from products p 
where exists ( select * from   categories c where c.category_id  = p.category_id);

Exists Results

product_idcategory_idproduct_namepricerelease_date
10272MicroService87.992019-05-13
10282Headphone 450BT97.992020-02-04
10292HD 1000XM3107.992018-08-05
10302HD SoundES18117.992017-01-01
10211APPLE 700400.992019-09-20
10221Samsung 5357.992019-08-23
10353GalatS6187.992019-10-13
10363MS250.992017-06-13
10373Dell280.992017-07-13
Table 4 Exists Operator

Both queries return the same data set which doesn’t have the record with category_id=33 from the products table.

6. Exists vs Join

The Join clause is used to list joined table’s columns

Join Clause

select * from products p , categories c
where p.category_id = c.category_id ;

Join Results

product_idcategory_idproduct_namepricerelease_datecategory_idcategory_name
10272MicroService87.992019-05-132Headphone
10282Headphone 450BT97.992020-02-042Headphone
10292HD 1000XM3107.992018-08-052Headphone
10302HD SoundES18117.992017-01-012Headphone
10211APPLE 700400.992019-09-201Mobile
10221Samsung 5357.992019-08-231Mobile
10353GalatS6187.992019-10-133Tablet
10363MS250.992017-06-133Tablet
10373Dell280.992017-07-133Tablet
Table 5 JOIN Clause

Note: the Join clause combines both tables’ columns.

7. Download the Source Code

Download
You can download the full source code of this example here: SQL EXISTS Operator

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button