DocsFundamentalsOperators

Operators

Fundamentals

Concept

SQL operators let you build rich conditions beyond simple equality checks. Logical operators AND, OR, and NOT combine or negate conditions. AND requires both sides to be true, OR requires at least one, and NOT flips the truth value. Proper use of parentheses is essential when mixing AND and OR because AND has higher precedence.

The IN operator checks whether a value exists in a list or subquery result set — it is a clean alternative to writing many OR conditions. BETWEEN provides inclusive range checks and works with numbers, dates, and strings. LIKE enables pattern matching with % (any sequence of characters) and _ (single character) as wildcards.

Understanding operator precedence prevents subtle bugs. SQL evaluates NOT first, then AND, then OR. When in doubt, add parentheses to make intent explicit. This is especially important in WHERE clauses with mixed logic.

Syntax

-- IN operator
SELECT * FROM products
WHERE category IN ('Electronics', 'Books', 'Games');

-- BETWEEN (inclusive on both ends)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- LIKE with wildcards
SELECT * FROM customers
WHERE last_name LIKE 'Sm%';     -- starts with 'Sm'

-- NOT combined with IN
SELECT * FROM products
WHERE category NOT IN ('Archived', 'Draft');

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Find customers whose name starts with 'J' and live in
-- New York or California, with orders over $100
SELECT
  c.first_name,
  c.last_name,
  c.state,
  o.total_amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.first_name LIKE 'J%'
  AND c.state IN ('NY', 'CA')
  AND o.total_amount > 100;

-- Products priced between $20 and $50,
-- excluding the 'Clearance' category
SELECT name, price, category
FROM products
WHERE price BETWEEN 20 AND 50
  AND category <> 'Clearance';

Common Pitfalls & Tips

1BETWEEN is inclusive on both ends. BETWEEN 1 AND 10 includes 1 and 10. This catches many people off guard with date ranges.
2IN with a NULL in the list can produce unexpected results — NULL IN (1, 2, NULL) is unknown, not true.
3LIKE '%term%' cannot use a regular index and forces a full table scan. For large tables consider full-text search.

Practice operators and conditions