IN and BETWEEN Operators

IN checks whether a column value matches any value in a specified list. WHERE city IN ('Seattle', 'Austin', 'New York') returns rows where city is any of the three cities. It is exactly equivalent to multiple OR conditions using equality: WHERE city = 'Seattle' OR city = 'Austin' OR city = 'New York'. The query optimiser typically converts IN to OR internally, so they produce identical execution plans and have identical performance for hardcoded lists.

IN is preferred over multiple OR conditions for three reasons. First, readability — IN lists the values once, clearly, without repeating the column name before every value. With five values, OR requires writing the column name and = operator five times. IN writes it once. Second, maintainability — adding a new value to an IN list requires adding one value inside the parentheses. Adding a value to OR conditions requires adding a full new OR clause. Third, correctness — IN with many values is harder to accidentally miswrite than a long chain of OR conditions, which requires careful attention to whether each uses = (not <> or >= by mistake).

IN also accepts a subquery instead of a hardcoded list: WHERE customer_id IN (SELECT customer_id FROM orders WHERE ...). This is dynamic IN — the list of values is computed by the subquery at runtime rather than hardcoded in the query. The subquery must return exactly one column. This pattern is a simpler alternative to JOIN for existence checks, though JOINs are often preferred for performance on large datasets.

BETWEEN checks whether a value falls within a range. BETWEEN lower AND upper is exactly equivalent to column >= lower AND column <= upper. Both boundaries are inclusive — the lower bound and the upper bound are included in the result set. A row where unit_price = 50 is returned by WHERE unit_price BETWEEN 50 AND 200, and so is a row where unit_price = 200.

BETWEEN works on numeric, date, and text columns. For numbers and dates, the range semantics are intuitive. For text columns, BETWEEN uses lexicographic (dictionary) order — WHERE name BETWEEN 'A' AND 'M' returns names that fall alphabetically between A and M inclusive. This is valid but less commonly used.

NOT BETWEEN is the inverse: NOT BETWEEN lower AND upper returns rows where the value is strictly less than lower OR strictly greater than upper — the boundaries are excluded from NOT BETWEEN results, consistent with the inclusive definition of BETWEEN itself.

BETWEEN is inclusive on both ends. When the column is a DATE type, '2024-01-31' means the entire day of January 31st, so BETWEEN '2024-01-01' AND '2024-01-31' correctly captures all of January. When the column is a TIMESTAMP or DATETIME type, '2024-01-31' is interpreted as '2024-01-31 00:00:00.000' — midnight at the start of January 31st. Any timestamp from 2024-01-31 00:00:01 to 2024-01-31 23:59:59 is greater than '2024-01-31 00:00:00' and therefore NOT between the two bounds. Records created during January 31st (except at exactly midnight) are silently excluded.

The impact in practice: if you use BETWEEN '2024-01-01' AND '2024-01-31' on a timestamp column for a January monthly report, you get all of January except orders placed on January 31st after midnight — which is most of January 31st. The report shows January 1-30 plus the very first second of January 31st. The discrepancy is subtle and can go unnoticed until someone cross-checks totals.

The correct approach for timestamp columns is always: WHERE timestamp_col >= '2024-01-01' AND timestamp_col < '2024-02-01'. The >= captures everything from the very start of January 1st. The strictly less than '2024-02-01' captures everything up to but not including the first moment of February — which is the entire last second of January 31st. This pattern works identically for DATE and TIMESTAMP columns and is unambiguous about what it includes. Adopt this as a strict professional standard: use >= and < for date range filtering, BETWEEN only when you are certain the column is DATE (not TIMESTAMP) and you need both ends inclusive.

When NULL appears in a NOT IN list, the entire NOT IN condition returns NULL for every row in the outer query — resulting in zero rows in the output. This is one of the most dangerous silent bugs in SQL.

The mechanism: NOT IN (val1, NULL) expands to: column <> val1 AND column <> NULL. The second condition — column <> NULL — always evaluates to NULL because any comparison with NULL returns NULL in three-valued logic. NOT NULL = NULL. Since WHERE discards rows where the condition is NULL, every single row is discarded regardless of what the column contains. The query returns zero rows with no error message.

This becomes a real problem when the NOT IN list comes from a subquery that might return NULLs: WHERE customer_id NOT IN (SELECT customer_id FROM orders). If any order has a NULL customer_id (which it might if customer_id is nullable), the entire NOT IN fails silently. The safe alternatives: use NOT EXISTS instead of NOT IN for subqueries — NOT EXISTS handles NULL correctly because it checks for the presence or absence of rows, not value equality. Or add WHERE customer_id IS NOT NULL to the subquery. As a professional rule: never use NOT IN with a subquery unless you are absolutely certain the subquery cannot return NULL. Default to NOT EXISTS for all subquery-based exclusion checks.

IN with a subquery and a JOIN can often express the same logical operation but have different performance characteristics and readability trade-offs. IN with a subquery is readable and semantically clear: WHERE customer_id IN (SELECT customer_id FROM orders WHERE ...) clearly communicates "find customers whose ID appears in this set." It is a good choice for simple existence checks on small to medium tables where readability matters more than micro-optimisation.

A JOIN is typically preferred over IN with a subquery for three reasons. Performance: most query optimisers convert IN subqueries to semi-joins internally, but explicit JOINs give the optimiser more flexibility to choose the most efficient join algorithm (hash join, merge join, nested loop) based on table statistics. For large tables, an explicit JOIN with proper indexing almost always outperforms IN with a subquery. Flexibility: a JOIN allows you to SELECT columns from both tables in the result. IN subquery only filters based on the subquery result — you cannot include columns from the subquery in the outer SELECT. Clarity for complex logic: multi-table queries with multiple IN subqueries become difficult to read and maintain, while the same logic expressed as JOINs with clear ON conditions and aliases is more organised.

Use IN with a subquery when: the query is simple and the subquery is small, you want to express an existence check without adding JOIN complexity, or you are writing a quick ad-hoc query. Use a JOIN when: tables are large and performance matters, you need columns from both tables in the result, or the query is complex enough that explicit JOIN structure aids maintainability. For production code in a team environment, explicit JOINs are the professional standard — they make the data flow visible and are easier to optimise with EXPLAIN ANALYZE.

Cause: The IN list or subquery for NOT IN contains at least one NULL value. NOT IN (val1, NULL) expands to col <> val1 AND col <> NULL. Since col <> NULL is always NULL (not TRUE), the entire condition evaluates to NULL for every row — and WHERE discards NULL results. The query returns zero rows silently, with no error message. This happens most often when the NOT IN list comes from a subquery that has nullable columns.

Fix: Replace NOT IN with NOT EXISTS: SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id). NOT EXISTS is always NULL-safe. Alternatively, add IS NOT NULL to the subquery: WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL). To diagnose: run the subquery alone and check: SELECT COUNT(*) FROM orders WHERE customer_id IS NULL — if this returns any rows, NOT IN will fail.

Cause: The column is a TIMESTAMP type but the BETWEEN boundary '2024-01-31' is interpreted as '2024-01-31 00:00:00'. Records created on January 31st after midnight — which is the entire working day — have timestamps greater than '2024-01-31 00:00:00', so they fall outside the BETWEEN range. The report silently excludes most of the last day.

Fix: Replace BETWEEN with >= and <: WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'. The strict less-than on the end captures all timestamps up to but not including the first moment of February — which includes all of January 31st. This pattern works correctly for both DATE and TIMESTAMP columns. Make this the team standard for all date range queries on timestamp columns.

Cause: Type mismatch between the column type and the IN list values. If price is an INTEGER or DECIMAL column and you provide string values ('100', '200') with quotes, PostgreSQL raises a type error. MySQL silently coerces the strings to numbers, which can produce unexpected results. The IN list values must match the column's data type.

Fix: Remove quotes from numeric values in the IN list: WHERE price IN (100, 200). Keep single quotes only for text values: WHERE status IN ('Delivered', 'Cancelled'). For dates, use ISO format strings: WHERE order_date IN ('2024-01-05', '2024-01-15'). If you are building IN lists dynamically in application code, use parameterised queries — the database driver handles type conversion correctly.

Cause: The lower and upper bounds in BETWEEN are reversed. BETWEEN requires the lower bound first and the upper bound second. BETWEEN 60000 AND 40000 is interpreted as salary >= 60000 AND salary <= 40000, which is always FALSE for any real salary value (no number can simultaneously be >= 60000 and <= 40000). The query returns zero rows.

Fix: Always write BETWEEN with the smaller value first: WHERE salary BETWEEN 40000 AND 60000. This is equivalent to salary >= 40000 AND salary <= 60000. Some databases (like MySQL) automatically swap reversed BETWEEN bounds, but PostgreSQL does not — zero rows are returned silently. If you are unsure of the order, use explicit >= and <= instead: WHERE salary >= 40000 AND salary <= 60000 — the order of the conditions does not matter.

Cause: The subquery inside IN returns more than one column. IN requires the subquery to return exactly one column — the value to compare against. SELECT customer_id, first_name FROM customers returns two columns, so WHERE customer_id IN (SELECT customer_id, first_name FROM customers) fails with an error like 'subquery must return only one column'.

Fix: Select only the column needed for the comparison: WHERE customer_id IN (SELECT customer_id FROM customers WHERE ...). If you accidentally included extra columns, remove them. If you need data from multiple columns, use a JOIN or EXISTS instead of IN. EXISTS does not require a single-column subquery: WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id AND c.city = 'Seattle') — the 1 is a placeholder, EXISTS only cares whether any row exists.

The FreshCart weekly operations report needs three things from one combined query: Show all orders where (1) the store is one of the Seattle or Austin stores (ST001, ST002, ST003, ST004), AND (2) the total_amount is between ₹400 and ₹1,500, AND (3) the order_status is NOT one of 'Cancelled' or 'Returned'. Show order_id, store_id, order_date, order_status, payment_method, and total_amount. Sort by total_amount descending.