Oracle EXISTS

Summary: in this tutorial, you will learn how to use the Oracle EXISTS operator to test for the existence of rows.

Introduction to the Oracle EXISTS operator

The Oracle EXISTS operator is a Boolean operator that returns either true or false. The EXISTS operator is often used with a subquery to test for the existence of rows:

SELECT * FROM table_name WHERE EXISTS(subquery);
Code language: SQL (Structured Query Language) (sql)

The EXISTS operator returns true if the subquery returns any rows, otherwise, it returns false. In addition, the EXISTS operator terminates the processing of the subquery once the subquery returns the first row.

Oracle EXISTS examples

Let’s take some examples of using EXISTS operator to see how it works.

Oracle EXISTS with SELECT statement example

See the following customers and orders tables in the sample database:

Customers and Orders tables

The following example uses the EXISTS operator to find all customers who have the order.

SELECT name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders WHERE customer_id = c.customer_id ) ORDER BY name;
Code language: SQL (Structured Query Language) (sql)
Oracle EXISTS example

For each customer in the customers table, the subquery checks whether the customer appears on the orders table. If yes, then the EXISTS operator returns true and stops scanning the orders table. Otherwise, the EXISTS operator returns false if the subquery does not find the customer in the orders table.

The result if the EXISTS operator is used by the WHERE clause to retrieve the customer that makes the subquery returns any rows.

Note that Oracle ignores the select list in the subquery so you can use any column, literal value, expression, etc. In the query above, we used literal number 1.

Oracle EXISTS with UPDATE statement example

See the following warehouses and locations tables:

warehouses and locations tables

The following statement updates the names of the warehouses located in the US:

UPDATE warehouses w SET warehouse_name = warehouse_name || ', USA' WHERE EXISTS ( SELECT 1 FROM locations WHERE country_id = 'US' AND location_id = w.location_id );
Code language: SQL (Structured Query Language) (sql)

For each warehouse, the subquery checks whether its location is in the US or not. If yes, the EXISTS operator in the WHERE clause returns true that causes the outer query append the string ', USA' to the warehouse name. Otherwise, the UPDATE statement does nothing due to the condition is the WHERE clause is false.

The following query verifies the update:

SELECT warehouse_name FROM warehouses INNER JOIN locations USING(location_id) WHERE country_id = 'US';
Code language: SQL (Structured Query Language) (sql)
Oracle EXISTS with UPDATE statement example

Oracle EXISTS with INSERT statement example

Suppose, we have to send special appreciation emails to all customers who had orders in 2016. To do this, first, we create a new table to store the data of customers:

CREATE TABLE customers_2016( company_id NUMBER GENERATED BY DEFAULT AS IDENTITY, company varchar2(255) NOT NULL, first_name varchar2(255) NOT NULL, last_name varchar2(255) NOT NULL, email varchar2(255) NOT NULL, sent_email CHAR(1) DEFAULT 'N', PRIMARY KEY(company_id) );
Code language: SQL (Structured Query Language) (sql)

Then, we insert customers who had orders in 2016 into the customers_2016 table:

INSERT INTO customers_2016( company, first_name, last_name, email ) SELECT name company, first_name, last_name, email FROM customers c INNER JOIN contacts ON contacts.customer_id = c.customer_id WHERE EXISTS( SELECT * FROM orders WHERE customer_id = c.customer_id AND EXTRACT( YEAR FROM order_date ) ORDER BY company;
Code language: SQL (Structured Query Language) (sql)

The following statement retrieves data from the customers_2016 table to verify the insert:

Oracle EXISTS with INSERT statement example

Oracle EXISTS vs. IN

The EXISTS operator stops scanning rows once the subquery returns the first row because it can determine the result whereas the IN operator must scan all rows returned by the subquery to conclude the result.

In addition, the IN clause can’t compare anything with NULL values, but the EXISTS clause can compare everything with NULL values. For example, the first statement returns no row while the second one returns all rows from the customers table:

SELECT * FROM customers WHERE customer_id IN(NULL); SELECT * FROM customers WHERE EXISTS ( SELECT NULL FROM dual );
Code language: SQL (Structured Query Language) (sql)

Typically, the EXISTS operator is faster than IN operator when the result set of the subquery is large. By contrast, the IN operator is faster than EXISTS operator when the result set of the subquery is small.

In this tutorial, you have learned how to use the Oracle EXISTS operator for testing existence of the rows in a query.

Was this tutorial helpful?