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
);

-- Use IN to get customers who have placed an order.
SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

-- Use NOT IN to get customers who have never placed an order.
SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- Use EXISTS to get customers with orders (faster for large datasets).
SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);

-- Use NOT EXISTS to get product categories with no inventory.
SELECT cat.category_name
FROM categories AS cat
WHERE NOT EXISTS (
    SELECT 1 FROM products AS p WHERE p.category_id = cat.category_id
);

-- Customers with orders (IN and EXISTS return the same result).
customer_name
-----------
Taro Yamada
Hanako Suzuki
Jiro Sato

-- Customers who have never placed an order.
customer_name
-----------
Megumi Tanaka
Ichiro Takahashi

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

SELECT c.customer_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.customer_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.