SQL In | Academic Block

SQL IN

Learn how to use the SQL IN operator to match a value against multiple possible values.

What is the IN Operator?

The IN operator is used with the WHERE clause to check whether a value matches any value in a specified list. It is a convenient alternative to writing multiple OR conditions.

The IN operator can be used with text values, numbers, and subqueries. The examples below use self-contained SQLite syntax so each Test My Code example can run independently.

Basic IN Syntax

The basic syntax places the column name before IN and a list of possible values inside parentheses.

WITH products(name) AS (
  VALUES
    ('Laptop'),
    ('Tablet'),
    ('Monitor'),
    ('Keyboard')
)
SELECT name
FROM products
WHERE name IN ('Laptop', 'Monitor');

IN with Text Values

You can use IN to find rows where a text column matches one of several specified values.

WITH employees(name, department) AS (
  VALUES
    ('Aarav', 'Sales'),
    ('Meera', 'IT'),
    ('Rohan', 'Finance'),
    ('Anika', 'HR'),
    ('Vikram', 'IT')
)
SELECT name, department
FROM employees
WHERE department IN ('IT', 'Finance');

IN with Numbers

The IN operator can also compare numeric values. Numeric values do not need quotation marks.

WITH products(name, price) AS (
  VALUES
    ('Mouse', 500),
    ('Keyboard', 900),
    ('Monitor', 1500),
    ('Webcam', 900)
)
SELECT name, price
FROM products
WHERE price IN (500, 900);

IN vs OR

IN can make a query shorter and easier to read when checking the same column against several values.

WITH cities(name) AS (
  VALUES
    ('Delhi'),
    ('Mumbai'),
    ('Chennai'),
    ('Kolkata'),
    ('Pune')
)
SELECT name
FROM cities
WHERE name IN ('Delhi', 'Mumbai', 'Pune');

The same condition could be written using OR:

WITH cities(name) AS (
  VALUES
    ('Delhi'),
    ('Mumbai'),
    ('Chennai'),
    ('Kolkata'),
    ('Pune')
)
SELECT name
FROM cities
WHERE name = 'Delhi'
   OR name = 'Mumbai'
   OR name = 'Pune';

NOT IN

The NOT IN operator returns rows whose value does not match any value in the specified list.

WITH products(name, category) AS (
  VALUES
    ('Laptop', 'Electronics'),
    ('Desk', 'Furniture'),
    ('Chair', 'Furniture'),
    ('Camera', 'Electronics'),
    ('Notebook', 'Stationery')
)
SELECT name, category
FROM products
WHERE category NOT IN ('Furniture', 'Stationery');

IN with AND

You can combine IN with other conditions using the AND operator.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 75000),
    ('Meera', 'Finance', 68000),
    ('Rohan', 'Sales', 55000),
    ('Anika', 'HR', 62000),
    ('Vikram', 'IT', 82000)
)
SELECT name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
AND salary > 70000;

IN with OR

IN can also be combined with other conditions using OR.

WITH products(name, category, price) AS (
  VALUES
    ('Laptop', 'Electronics', 800),
    ('Chair', 'Furniture', 120),
    ('Camera', 'Electronics', 600),
    ('Desk', 'Furniture', 300)
)
SELECT name, category, price
FROM products
WHERE category IN ('Electronics')
   OR price < 150;

IN with ORDER BY

You can use ORDER BY to sort the results returned by an IN condition.

WITH products(name, category, price) AS (
  VALUES
    ('Laptop', 'Electronics', 800),
    ('Chair', 'Furniture', 120),
    ('Camera', 'Electronics', 600),
    ('Desk', 'Furniture', 300),
    ('Phone', 'Electronics', 700)
)
SELECT name, category, price
FROM products
WHERE category IN ('Electronics', 'Furniture')
ORDER BY price DESC;

IN with LIMIT

SQLite also allows IN to be combined with LIMIT when only a specific number of matching rows should be returned.

WITH students(name, grade) AS (
  VALUES
    ('Aman', 'A'),
    ('Neha', 'B'),
    ('Rahul', 'A'),
    ('Priya', 'C'),
    ('Karan', 'A')
)
SELECT name, grade
FROM students
WHERE grade IN ('A', 'B')
ORDER BY name
LIMIT 3;

IN with a Subquery

The IN operator can compare a value with the results returned by a subquery.

WITH employees(name, department_id) AS (
  VALUES
    ('Aarav', 1),
    ('Meera', 2),
    ('Rohan', 3),
    ('Anika', 1)
),
departments(id, department) AS (
  VALUES
    (1, 'IT'),
    (2, 'Finance'),
    (3, 'Sales')
)
SELECT name
FROM employees
WHERE department_id IN (
  SELECT id
  FROM departments
  WHERE department IN ('IT', 'Sales')
);

IN with Dates

In SQLite, dates are commonly stored as text values in ISO format. Therefore, you can use IN to match specific dates.

WITH events(event_name, event_date) AS (
  VALUES
    ('Workshop', '2026-08-20'),
    ('Seminar', '2026-08-22'),
    ('Conference', '2026-08-24'),
    ('Meeting', '2026-08-25')
)
SELECT event_name, event_date
FROM events
WHERE event_date IN ('2026-08-22', '2026-08-24');

IN with Calculated Values

The expression being tested with IN does not have to be a simple column. SQLite can compare calculated values as well.

WITH products(name, price) AS (
  VALUES
    ('Keyboard', 500),
    ('Mouse', 300),
    ('Monitor', 1500),
    ('Webcam', 700)
)
SELECT name, price
FROM products
WHERE price * 2 IN (600, 1000, 1400);

IN and NULL Values

Be careful when using IN with NULL. SQL uses three-valued logic, so comparisons involving NULL do not behave like ordinary value comparisons.

If you need to find NULL values, use IS NULL instead of IN (NULL).

WITH employees(name, department) AS (
  VALUES
    ('Aarav', 'IT'),
    ('Meera', NULL),
    ('Rohan', 'Sales')
)
SELECT name, department
FROM employees
WHERE department IS NULL;

Common Uses of IN

Use	Example	Purpose
Text values	department IN (‘IT’, ‘HR’)	Match multiple text values
Numbers	price IN (100, 200, 500)	Match multiple numbers
NOT IN	category NOT IN (‘Used’)	Exclude specified values
Subquery	id IN (SELECT id …)	Match values returned by another query

Advantages of IN

Makes multiple-value comparisons easier to read.
Reduces the need for repeated OR conditions.
Works with both text and numeric values.
Can be combined with AND, OR, ORDER BY, and LIMIT.
Can be used with subqueries.

Best Practices

Use IN when checking one value against several possible values.
Use NOT IN when you need to exclude a list of values.
Use IS NULL when checking for NULL values.
Use a subquery with IN when the list of values comes from another query.
Keep the list of values concise and easy to understand.

The IN operator provides a simple way to compare a value against multiple possible values. It makes SQL queries cleaner than repeatedly using OR and can also work with subqueries for more advanced filtering.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.