SQL | EXISTS

The SQL EXISTS operator is used to test whether a subquery returns at least one row. It helps filter results by checking the presence of related data, making it useful for validating relationships between tables.

Example: First, we create a demo SQL database and tables, on which we use the EXISTS command.

Customer Table :

Order Table :

Query:

SELECT Name
FROM Customers c
WHERE EXISTS (
    SELECT 1 
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

Output:

Syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (
    SELECT column_name(s)
    FROM subquery_table
    WHERE condition
);

• EXISTS: The boolean operator that checks if a subquery returns rows.
• Subquery: A nested SELECT query that returns data for evaluation.
• Condition: The condition applied to the subquery.

Examples of SQL EXISTS

Consider the following two relation "Customers" and "Orders".

Customers Table:

Orders Table:

Example 1 : Using EXISTS with SELECT

To fetch the customers whose website is shared by at least one other customer in the same Customers table.

Query:

SELECT c1.*
FROM Customers c1
WHERE EXISTS (
    SELECT 1
    FROM Customers c2
    WHERE c2.customer_id = c1.customer_id
      AND c2.website = 'abc.com'
);

Output:

Example 2 : Using NOT with EXISTS

Fetch last and first name of the customers who has not placed any order.

SELECT last_name, first_name
FROM Customers
WHERE NOT EXISTS (SELECT * 
                  FROM Orders 
                  WHERE Customers.customer_id = Orders.c_id);

Output:

Example 3 : Using EXISTS condition with DELETE statement

Delete the record of all the customer from Order Table whose last name is 'Mehra'.

DELETE 
FROM Orders
WHERE EXISTS (SELECT *
              FROM customers
              WHERE Customers.customer_id = Orders.c_id
              AND Customers.last_name = 'Luna');

SELECT * FROM Orders;

Output:

Example 4 : Using EXISTS condition with UPDATE statement

Update the lname as 'Kumari' of customer in Customer Table whose customer_id is 401.

UPDATE Customers
SET lname = 'Kumari'
WHERE EXISTS (
    SELECT 1
    FROM Customers c2
    WHERE c2.customer_id = 401
      AND c2.customer_id = Customers.customer_id
);

SELECT * FROM Customers;

Output:

Differences Between EXISTS and IN

Here is the detailed comparison between EXISTS and IN