The SQL EXISTS
operator tests the existence of any value in a subquery i.e. it executes the outer SQL query only if the subquery is not NULL
(empty result-set).
-- select customer id and first name of customers
-- whose order amount is less than 12000
SELECT customer_id, first_name
FROM Customers
WHERE EXISTS (
SELECT order_id
FROM Orders
WHERE Orders.customer_id = Customers.customer_id AND amount < 12000
);
Here, the SQL query:
The syntax of the SQL EXISTS
operator is:
SELECT column1, column2, ...
FROM table
WHERE EXISTS(SUBQUERY);
Here,
column1, column2, ...
are the column names to filtertable
refers to the name of the tableEXISTS
tests the result of the subquerySUBQUERY
can be any SQL query-- select customer id and first name of customers from Customers table
-- if the customer id exists in the Orders table
SELECT customer_id, first_name
FROM Customers
WHERE EXISTS (
SELECT order_id
FROM Orders
WHERE Orders.customer_id = Customers.customer_id
);
Here is how the SQL command works:
Working: EXISTS in SQL
This process is repeated for each row of the outer query.
Example: EXISTS in SQL
We can also use the NOT operator to inverse the working of the EXISTS
clause. The SQL command executes if the subquery returns an empty result-set.
For example,
-- select customer id and first name from Customers table
-- if the customer id doesn't exist in the Orders table
SELECT customer_id, first_name
FROM Customers
WHERE NOT EXISTS (
SELECT order_id
FROM Orders
WHERE Orders.customer_id = Customers.customer_id
);
Here, the SQL command returns a row from the Customers table if the related row is not in the Orders table.