SQL IN

What is the SQL IN operator?

IN filters rows where a column’s value matches any value in a specified list or the result set of a subquery.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL IN: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, MariaDB

SQL IN Full Explanation

IN is a comparison operator that returns TRUE when the expression on its left equals at least one element in a parenthesized list of literals or the single-column result of a subquery. It is semantically identical to a series of OR-combined equality checks but is more concise and often clearer. When the list contains NULL, the operator ignores it because equality to NULL is unknown. The right-hand list must contain comparable data types, otherwise implicit casting rules apply and may change query plans. Modern optimizers can rewrite IN predicates into hash or semi-join operations for performance. NOT IN is the logical negation and behaves differently with NULLs: if any element in the list is NULL, NOT IN returns UNKNOWN for all rows, effectively filtering nothing unless handled with IS NOT NULL or COALESCE.

SQL IN Syntax

-- literal list form
expression IN (value1, value2, ...)

-- subquery form
expression IN (SELECT single_column FROM table_name WHERE conditions);

SQL IN Parameters

value_list (list) - Comma separated literals or expressions
subquery (query) - A subquery that returns one column
expression (expression) - The value being tested

Example Queries Using SQL IN

-- 1. Literal list
SELECT *
FROM orders
WHERE status IN ('shipped','delivered');

-- 2. Subquery list
SELECT o.*
FROM orders o
WHERE o.customer_id IN (
  SELECT id FROM customers WHERE vip = TRUE
);

-- 3. NOT IN with NULL-safe guard
SELECT *
FROM products p
WHERE p.id NOT IN (
  SELECT product_id FROM recalls WHERE product_id IS NOT NULL
);

Expected Output Using SQL IN

Each query returns only the rows whose tested column matches at least one value in the provided list or subquery (or does not match when NOT IN is used)

Use Cases with SQL IN

Filter by a finite set of known values
Join-like filtering without explicit JOIN syntax
Enforce business rules such as limiting results to approved status codes
Replace multiple OR predicates for readability
Semi-join pattern for existence checks when only left table columns are needed

Common Mistakes with SQL IN

Mixing incompatible data types in the value list causing implicit casts and slow scans
Using NOT IN with a list containing NULL leading to no rows returned
Forgetting parentheses around the list
Expecting IN to search substrings (use LIKE instead)

First Introduced In

SQL-92

Frequently Asked Questions

How is IN different from EXISTS?

IN materializes the right-hand list then compares equality, while EXISTS stops at the first matching row and can be correlated. Performance depends on data size and indexing.

Can I use IN with dates or numbers?

Yes. All literals in the list must be comparable to the tested expression’s data type.

How many values can I place inside IN?

Most engines support thousands of literals, but very large lists can bloat the query plan. Consider temporary tables or JOINs for huge sets.

Does IN respect ordering?

No. IN is a membership test, not an ordering clause. Use ORDER BY to sort the final result.