What is the difference between EXISTS and IN in SQL?

1 year ago
William Carter
2 minutes

In SQL, both EXISTS and IN are used for subqueries, but they have different purposes and behaviors.

  1. EXISTS is used to check if a subquery returns any results. If the subquery returns at least one row, EXISTS will return TRUE; otherwise, it returns FALSE. EXISTS is often used to connect an outer query with a subquery and filter the result set of the outer query based on the results of the subquery.

For example, the following query will return all customers who have at least one order:

SELECT *
FROM customers
WHERE EXISTS (
    SELECT *
    FROM orders
    WHERE orders.customer_id = customers.customer_id
);
  1. IN is used to check if a value exists in a given list or subquery. If the value is found in the list or subquery, IN will return TRUE; otherwise, it will return FALSE. IN is commonly used to filter rows with specific values.

For example, the following query will return all customers who have orders with a status of ‘Completed’:

SELECT *
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_status = 'Completed'
);

In conclusion:

  1. EXISTS is used to check if a subquery returns any results, commonly used in conjunction with an outer query.
  2. IN is used to check if a value exists in a list or subquery, often used to filter rows with specific values.
bannerAds