IN / EXISTS

IN: Checks whether a value is included in the subquery result.

SELECT column FROM table
WHERE column IN (SELECT column FROM subquery);

NOT IN: Returns rows where the value is not in the subquery result.

SELECT column FROM table
WHERE column NOT IN (SELECT column FROM subquery);

EXISTS: Evaluates to true if the subquery returns at least one row.

SELECT column FROM table AS a
WHERE EXISTS (
    SELECT 1 FROM subquery WHERE subquery.key = a.key
);

NOT EXISTS: Evaluates to true if the subquery returns no rows.

SELECT column FROM table AS a
WHERE NOT EXISTS (
    SELECT 1 FROM subquery WHERE subquery.key = a.key
);

Syntax	Description
IN (SELECT ...)	Returns rows where the value is included in the subquery result.
NOT IN (SELECT ...)	Returns rows where the value is not included in the subquery result. May produce unexpected results if NULL values are present.
EXISTS (SELECT ...)	Evaluates to true if the subquery returns at least one row.
NOT EXISTS (SELECT ...)	Evaluates to true if the subquery returns no rows.

The following customers and orders tables are used in the examples below.

Use IN to get customers who have placed an order.

sample_in_exists.sql

SELECT name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

+---------------+
| name          |
+---------------+
| Ryomen Sukuna |
| Gojo Satoru   |
| Itadori Yuji  |
+---------------+
3 rows in set

Use NOT IN to get customers who have never placed an order.

sample_in_exists.sql

SELECT name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

+------------------+
| name             |
+------------------+
| Fushiguro Megumi |
| Kugisaki Nobara  |
+------------------+
2 rows in set

Use EXISTS to get customers with orders (faster for large datasets). The 1 in SELECT 1 has no special meaning — it is simply a convention for checking whether a row exists.

sample_in_exists.sql

SELECT c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);

+---------------+
| name          |
+---------------+
| Ryomen Sukuna |
| Gojo Satoru   |
| Itadori Yuji  |
+---------------+
3 rows in set

Use NOT EXISTS to get customers who have never placed an order.

sample_in_exists.sql

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);

+------------------+
| name             |
+------------------+
| Fushiguro Megumi |
| Kugisaki Nobara  |
+------------------+
2 rows in set

The IN, NOT IN, EXISTS, and NOT EXISTS syntax is supported across all major databases.

SELECT c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);

However, support for the AS keyword in table aliases varies by database. In Oracle, you omit AS and write the alias directly after the table name.

-- Oracle (table aliases without AS)
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

IN and EXISTS often return the same result, but they have different performance characteristics. When the subquery returns a large number of rows, EXISTS is often faster. This is because EXISTS stops evaluating as soon as the subquery finds the first matching row.

If the subquery result for NOT IN contains even one NULL value, all rows evaluate to FALSE and the query returns zero rows. This is due to SQL's behavior where any comparison with NULL returns UNKNOWN. If NULL values may be present, use NOT EXISTS instead, or add WHERE column IS NOT NULL to the subquery.

For comparisons that return a single value from a subquery, see Subquery (Scalar / Row). For comparisons against all or any values, see ALL / ANY.