-- IN with literal values

SELECT * FROM orders WHERE status IN ('active', 'pending', 'shipped');

-- IN with subquery

SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

-- BETWEEN for ranges

SELECT * FROM products WHERE price BETWEEN 10 AND 50;

-- EXISTS for existence check

SELECT * FROM customers c

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- NOT IN, NOT BETWEEN, NOT EXISTS

SELECT * FROM products WHERE category NOT IN ('electronics', 'books');

SELECT * FROM orders WHERE order_date NOT BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

IN tests value against list of values

BETWEEN is inclusive (includes both endpoints)

EXISTS returns true if subquery has rows

NOT IN, NOT BETWEEN, NOT EXISTS negate conditions

IN can use literal values or subquery

EXISTS typically used with correlated subqueries

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

AND

Requires every condition in the boolean expression to evaluate to TRUE.

condition_a AND condition_b

AND has higher precedence than OR.

IN

Matches a value against a fixed list or subquery result set.

department IN ('Sales', 'Marketing')

Cleaner than repeating many OR conditions.

BETWEEN