SQL comparison operators test relationships between values, returning TRUE, FALSE, or UNKNOWN. Logical operators (AND, OR, NOT) combine these boolean results to build compound conditions. Together they form the complete filtering vocabulary of SQL.

**Comparison Operators**

The six standard comparison operators work on numbers, strings, and dates:

= (equals), != or <> (not equals), < (less than), > (greater than), <= (less than or equal), >= (greater than or equal).

String comparisons are collation-dependent — case sensitivity varies by database and collation settings.

**Logical Operators: AND, OR, NOT**

AND requires BOTH conditions to be TRUE.

OR requires AT LEAST ONE condition to be TRUE.

NOT inverts the boolean result.

Precedence order (highest to lowest): NOT → AND → OR.

Always use parentheses when mixing AND and OR to make intent explicit.

**BETWEEN Operator**

BETWEEN low AND high is inclusive on BOTH ends (equivalent to col >= low AND col <= high).

Use explicit >= / <= for clarity on datetime boundaries to avoid end-of-day traps.

**IN Operator**

IN (val1, val2, ...) is shorthand for col = val1 OR col = val2 OR ...

IN with a subquery checks membership: col IN (SELECT id FROM ...).

NOT IN with a subquery is dangerous if the subquery returns any NULL — the entire result becomes empty.

**LIKE and Pattern Matching**

% matches zero or more characters. _ matches exactly one character.

LIKE is case-insensitive in MySQL by default; case-sensitive in PostgreSQL unless using ILIKE.

Leading wildcard (%pattern) is NOT sargable — it forces a full table scan.

**IS NULL / IS NOT NULL**

NULL comparisons MUST use IS NULL or IS NOT NULL — never = NULL (always returns UNKNOWN).

**ALL, ANY, SOME**

col > ALL (subquery): col must be greater than every value in the subquery result.

col > ANY (subquery): col must be greater than at least one value. SOME is a synonym for ANY.

Comparison and logical operators appear in 100% of data retrieval queries. Misunderstanding operator precedence or NULL behavior causes silent data bugs that can affect business decisions, billing, and reporting.

Every meaningful SQL query uses comparison and logical operators. Filtering customers by status, date range, or category; combining multiple conditions for fraud detection; pattern-matching email addresses — these operators are the fundamental building blocks of all WHERE clauses.