-- Comparison operators

SELECT * FROM orders WHERE amount > 100;

SELECT * FROM users WHERE status != 'inactive';

SELECT * FROM products WHERE price BETWEEN 10.00 AND 99.99; -- inclusive both ends

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

SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- ends with

SELECT * FROM users WHERE phone IS NULL;

-- Logical operators with precedence

-- WITHOUT parentheses (AND binds tighter than OR):

WHERE dept = 'Sales' OR dept = 'Marketing' AND salary > 80000

-- Interpreted as: dept = 'Sales' OR (dept = 'Marketing' AND salary > 80000)

-- WITH parentheses (explicit intent):

WHERE (dept = 'Sales' OR dept = 'Marketing') AND salary > 80000

-- NOT operator

SELECT * FROM products WHERE NOT discontinued;

SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');

SELECT * FROM users WHERE NOT (age < 18 OR verified = FALSE);

NULL comparisons must use IS NULL or IS NOT NULL — never = NULL

AND has higher precedence than OR — use parentheses with mixed conditions

BETWEEN is inclusive on both ends: BETWEEN 1 AND 10 includes 1 and 10

NOT IN with a nullable subquery column is unsafe — use NOT EXISTS

LIKE '%value' (leading %) is not sargable; LIKE 'value%' (trailing %) is

ANY and ANY/SOME mean the same thing — ANY is preferred in modern SQL

LIKE is case-insensitive by default (depends on collation). Use REGEXP for more powerful patterns. ILIKE is not supported.

LIKE is case-sensitive. Use ILIKE for case-insensitive matching. Supports SIMILAR TO (regex-based). ~ and !~ for POSIX regex comparisons.

LIKE is case-insensitive by default (depends on collation). Supports [charlist] and [^charlist] wildcards in LIKE patterns. Use PATINDEX() for pattern position.

LIKE is case-sensitive. No ILIKE — use UPPER(col) LIKE UPPER('%pattern%'). Supports REGEXP_LIKE() for regex matching.

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.

!= / <>

Returns rows where the compared values are not equal.

column <> value

SQL supports both <> and != in many engines, but <> is the portable form.