CodeCapsule

SQL IN operator

by

Here is a clear and practical guide to the SQL IN operator, with examples, performance notes, and best practices.

✅ What the IN Operator Does

IN allows you to test whether a value matches any value in a list or any value returned by a subquery.

It simplifies multiple OR conditions.

🔹 Basic Syntax

WHERE column_name IN (value1, value2, value3, ...)

Equivalent to:

WHERE column_name = value1
OR column_name = value2
OR column_name = value3

✅ Basic Examples

1. Match any value in a list

SELECT *
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');

2. NOT IN (exclude values)

SELECT *
FROM employees
WHERE department NOT IN ('HR', 'Finance');

⚠ Warning: NOT IN behaves differently when the list contains NULL
(see notes below).

🔥 IN with Subquery

Common for relational filtering:

SELECT *
FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE is_active = 1
);

🔥 IN with Numbers / IDs

SELECT *
FROM orders
WHERE id IN (101, 102, 205, 330);

🧠 Important Behavior Notes

✔ IN is NULL-safe (but NOT IN is not)

  • IN ignores NULL in the list

  • NOT IN returns no rows if the list contains NULL

Example (dangerous):

WHERE id NOT IN (1,2,NULL) -- returns ZERO rows!

Best fix:
Use NOT EXISTS instead of NOT IN.

⚡ Performance Tips

✔ For large lists, subqueries or JOINs may perform better
✔ Ensure the column in the IN clause is indexed
✔ Avoid extremely long IN lists
✔ For big datasets, consider replacing with JOIN

✔ Real-World Use Cases

1. Select customers in selected regions

SELECT * FROM customers
WHERE region IN ('North', 'West', 'South');

2. Get orders placed by premium users

SELECT * FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE account_type = 'premium'
);

3. Exclude system user IDs

SELECT * FROM sessions
WHERE user_id NOT IN (0, -1);

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *