Chapter 28: PostgreSQL IN Operator
The IN operator.
You already know =, LIKE, BETWEEN, AND/OR… Now comes IN — which basically says:
“Is this value equal to any of the values in this list?”
It’s the polite, short way of writing many OR conditions without making your query look ugly.
1. What does the IN operator actually do?
IN checks whether a value matches any value in a given list (or subquery result).
Syntax feels very natural:
|
0 1 2 3 4 5 6 7 |
column_name IN (value1, value2, value3, …) column_name NOT IN (value1, value2, value3, …) |
Equivalent (but ugly) long version:
|
0 1 2 3 4 5 6 7 8 9 |
column_name = value1 OR column_name = value2 OR column_name = value3 … |
PostgreSQL (and almost all SQL databases) treats IN exactly like a series of = OR = OR …
2. Two main flavors you’ll use every day
| Flavor | Syntax example | When to use it |
|---|---|---|
| Static list (most common) | status IN (‘pending’, ‘shipped’, ‘delivered’) | Fixed known values (statuses, codes, cities) |
| Subquery (very powerful) | id IN (SELECT customer_id FROM orders WHERE …) | Dynamic list coming from another table/query |
3. Real, practical examples (using our ongoing students & bookstore_demo tables)
Students table (quick reminder)
| id | first_name | last_name | enrollment_year | city | status | |
|---|---|---|---|---|---|---|
| 1 | Aarav | Patel | aarav.patel@hyduni.edu | 2025 | Hyderabad | active |
| 2 | Priya | Reddy | priya.reddy22@gmail.com | 2024 | Secunderabad | active |
| 3 | Rahul | Sharma | rahul.sharma.2004@yahoo.com | 2025 | Hyderabad | inactive |
| 4 | Sneha | Kumar | sneha.kumar06@gmail.com | 2025 | Hyderabad | active |
| 5 | Meera | Iyer | meera.iyer@gmail.com | 2025 | Hyderabad | active |
Example 1: Simple static list – most common use
|
0 1 2 3 4 5 6 7 8 9 10 |
-- Students enrolled in 2024 or 2025 SELECT first_name, last_name, enrollment_year FROM students WHERE enrollment_year IN (2024, 2025) ORDER BY enrollment_year DESC; |
→ Returns all except very old years
Example 2: String list (very frequent in apps)
|
0 1 2 3 4 5 6 7 8 9 |
-- Only active or pending students SELECT first_name, status FROM students WHERE status IN ('active', 'pending'); |
→ Very readable compared to status = ‘active’ OR status = ‘pending’
Example 3: NOT IN – exclude certain values
|
0 1 2 3 4 5 6 7 8 9 |
-- Students NOT from Hyderabad or Secunderabad SELECT first_name, city FROM students WHERE city NOT IN ('Hyderabad', 'Secunderabad'); |
Example 4: IN with subquery (this is where it becomes powerful)
Imagine we have an orders table in bookstore_demo:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Customers who have placed at least one order SELECT first_name, last_name, email FROM customers WHERE id IN ( SELECT DISTINCT customer_id FROM orders WHERE status = 'delivered' ); |
→ Dynamic: list of IDs comes from another table
Example 5: IN + array (PostgreSQL bonus – very nice!)
PostgreSQL lets you use arrays with = ANY (equivalent to IN)
|
0 1 2 3 4 5 6 7 8 |
SELECT title, price FROM books WHERE publication_year = ANY(ARRAY[1997, 2008, 2010]); |
→ Same as publication_year IN (1997, 2008, 2010)
Many developers prefer = ANY when the list comes from a variable/array in application code.
4. Important rules & gotchas (2026 PostgreSQL reality)
| Situation | What happens | Correct / Best practice |
|---|---|---|
| IN () empty list | Always false (no match) | Avoid empty lists in production code |
| column IN (NULL) | Never true (even if column is NULL) | Use IS NULL separately if needed |
| column NOT IN (1,2,3) when column can be NULL | If any row has NULL → whole condition = NULL (false) | Be very careful — prefer NOT IN + IS NOT NULL |
| Very long list (1000+ values) | Can be slow, hard to read | Prefer subquery / temp table / = ANY(VALUES …) |
| Performance | Usually uses index if column is indexed | Index the column if you filter often |
| IN vs = for single value | IN (single) is same as = | Use = for clarity when only one value |
Very common NULL trap example:
|
0 1 2 3 4 5 6 |
SELECT * FROM students WHERE status NOT IN ('active', 'pending'); |
→ If any student has status = NULL → that row will not appear (because NULL NOT IN … is NULL → false)
Correct (if you want NULLs excluded too):
|
0 1 2 3 4 5 6 7 |
WHERE status NOT IN ('active', 'pending') AND status IS NOT NULL |
5. Quick cheat-sheet (keep in your notes)
| Goal | Best syntax in PostgreSQL 2026 | Alternative (older style) |
|---|---|---|
| Match any of 3–10 fixed values | status IN (‘pending’,’shipped’,’delivered’) | status = ‘pending’ OR … |
| Exclude fixed values | city NOT IN (‘Hyderabad’,’Delhi’) | — |
| Dynamic list from another table | id IN (SELECT …) | EXISTS (SELECT 1 FROM … WHERE …) |
| List from array variable (in app code) | year = ANY($1::int[]) | year IN (VALUES …) |
| Single value | status = ‘active’ | status IN (‘active’) (works but ugly) |
Your mini practice right now (try in your database)
- Select students whose enrollment_year is 2024 or 2025
- Select books whose price is in (199, 299, 399, 450)
- Select customers NOT in a certain city list
- Write a query using IN (subquery) to find students who have GPA above average
Next class?
Tell me:
- Want BETWEEN and IN comparison / combination?
- EXISTS vs IN (subquery performance & readability)?
- = ANY vs IN with arrays in detail?
- Or move to another popular operator/function (EXISTS, ANY/ALL, STRING_AGG, etc.)?
Your teacher is ready — fire away! 🚀
