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 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);
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.
Let’s take some examples of using
EXISTS operator to see how it works.
SELECT statement example
See the following
orders tables in the sample database:
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;
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
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.
UPDATE statement example
See the following
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 );
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';
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) );
Then, we insert customers who had orders in 2016 into the
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;
The following statement retrieves data from the
customers_2016 table to verify the insert:
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
SELECT * FROM customers WHERE customer_id IN(NULL); SELECT * FROM customers WHERE EXISTS ( SELECT NULL FROM dual );
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.