IN, BETWEEN, LIKE — concise filtering

These operators clean up common patterns that would otherwise require many OR conditions.

IN — match a list of values

-- Instead of: WHERE department_id = 60 OR department_id = 80 OR department_id = 90
SELECT first_name, department_id
FROM employees
WHERE department_id IN (60, 80, 90);

NOT IN excludes the listed values:

WHERE department_id NOT IN (10, 20)

Caution: NOT IN returns no rows if the list contains a NULL. Use NOT EXISTS or IS NOT NULL guards when NULL is possible.

BETWEEN — inclusive range check

-- Products priced 30 to 80 (both endpoints included)
SELECT name, price
FROM products
WHERE price BETWEEN 30 AND 80;

Equivalent to price >= 30 AND price <= 80.

LIKE — pattern matching

% matches any sequence of characters. _ matches exactly one character.

-- Last names starting with 'K'
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'K%';

-- Exactly 5-character last names
WHERE last_name LIKE '_____';

ILIKE — case-insensitive LIKE (PostgreSQL extension)

WHERE last_name ILIKE 'k%'   -- matches King, king, KING

Tips

• BETWEEN works on dates too: WHERE hire_date BETWEEN '2000-01-01' AND '2010-12-31'.
• LIKE patterns with a leading % (e.g., '%king') cannot use an index — they force a full scan.
• For large string searches, consider pg_trgm or full-text search.