SQL | EXISTS

The SQL EXISTS condition is used to test whether a correlated subquery returns any results. If the subquery returns at least one row, the EXISTS condition evaluates to TRUE; otherwise, it evaluates to FALSE. The EXISTS operator can be used in various SQL statements like SELECT, UPDATE, INSERT, and DELETE to filter data based on whether certain conditions are met.

What is SQL EXISTS?

The EXISTS condition is primarily used to check the existence of rows returned by a subquery. It’s commonly used in scenarios where you need to check if a record exists in a related table, and you want to perform an action based on that result.

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 first and last name of the customers who placed atleast one order.

Query:

SELECT fname, lname 
FROM Customers 
WHERE EXISTS (SELECT * 
              FROM Orders 
              WHERE Customers.customer_id = Orders.c_id);

Output:

Example 2 : Using NOT with EXISTS

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

SELECT lname, fname
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.lname = 'Mehra');

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 *
              FROM Customers
              WHERE customer_id = 401);

SELECT * FROM Customers;

Output:

When to Use SQL EXISTS

The EXISTS condition is particularly useful in the following scenarios:

• Checking Data Existence: You can use EXISTS to check if related data exists in another table before performing an action (e.g., selecting, updating, deleting).
  
• Performance: EXISTS is often more efficient than using IN when dealing with large datasets, as it stops searching once a match is found.
  
• Correlated Subqueries: EXISTS is ideal for correlated subqueries, where the subquery refers to the outer query’s values.

Differences Between EXISTS and IN

• EXISTS is used for checking the existence of rows, while IN checks if a value matches any value from a list or subquery result.
  
• EXISTS is more efficient when the subquery results in a large number of rows.
  
• IN works well for small datasets or static lists of values.

Conclusion

The SQL EXISTS condition is an essential tool for database professionals, helping to test the existence of records in a subquery. By using EXISTS, you can perform various operations such as SELECT, UPDATE, INSERT, and DELETE based on the presence of related data. Whether you're checking if customers have placed orders or performing data integrity checks, EXISTS provides a powerful, efficient way to filter and manipulate data.