IN, BETWEEN & More
Advanced filtering operators
These operators make complex filtering conditions easier to read and write.
IN Operator
Check if a value matches any value in a list:
-- Instead of multiple OR conditions
SELECT * FROM users
WHERE country IN ('USA', 'Canada', 'Mexico');
-- Equivalent to:
SELECT * FROM users
WHERE country = 'USA'
OR country = 'Canada'
OR country = 'Mexico';
-- NOT IN
SELECT * FROM orders
WHERE status NOT IN ('cancelled', 'refunded');
-- IN with subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);BETWEEN Operator
Check if a value is within a range (inclusive):
-- Price range SELECT * FROM products WHERE price BETWEEN 10 AND 50; -- Equivalent to: SELECT * FROM products WHERE price >= 10 AND price <= 50; -- Date range SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'; -- NOT BETWEEN SELECT * FROM products WHERE price NOT BETWEEN 100 AND 500;
ANY / ALL (PostgreSQL)
-- ANY: Compare to any value in a list SELECT * FROM products WHERE price > ANY (SELECT price FROM products WHERE category = 'Budget'); -- ALL: Compare to all values SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'Budget');
EXISTS
Check if a subquery returns any rows:
-- Users who have placed at least one order
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id
);
-- Users with no orders
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id
);Performance: EXISTS often performs better than IN with subqueries because it stops as soon as it finds a match.