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:

• In a SELECT statement.
• In a INSERT statement.
• Compare the Exists operator with the In operator.
• Compare the Exists operator with the Join clause.

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

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

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

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

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

Note: the Join clause combines both tables’ columns.

7. Download the Source Code

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.