Comparison Operators Fundamentals

basic

Filter products using all six comparison operators.

-- All six comparison operators

SELECT product_name, price, stock_qty

FROM products

WHERE price >= 10.00 -- greater than or equal

AND price <= 99.99 -- less than or equal

AND stock_qty > 0 -- greater than

AND category_id != 5 -- not equal (also: <>)

AND discontinued = FALSE -- equals

AND rating < 2.0; -- less than

Returns active, in-stock products priced $10–$99.99 with a rating below 2.0.

Demonstrates all six comparison operators in a single query.

All

AND / OR / NOT Precedence

basic

Show why parentheses matter when mixing AND and OR.

-- BUG: AND binds tighter than OR — wrong intent

SELECT * FROM employees

WHERE department = 'Sales'

OR department = 'Marketing'

AND salary > 80000;

-- Interpreted as: Sales (any salary) OR (Marketing AND salary > 80000)

-- CORRECT: parentheses enforce intent

SELECT * FROM employees

WHERE (department = 'Sales' OR department = 'Marketing')

AND salary > 80000;

-- Now: both departments, only salary > 80000

The parenthesized version returns only high-salary Sales and Marketing employees.

AND has higher precedence than OR. Without parentheses, AND binds first, producing unexpected results.

All

IN, BETWEEN, LIKE Operators

basic

Use set membership, range, and pattern matching operators.

-- IN: set membership (cleaner than multiple ORs)

SELECT order_id, status, total

FROM orders

WHERE status IN ('pending', 'processing', 'shipped')

AND total BETWEEN 50.00 AND 500.00 -- inclusive range