-- LIKE with wildcards

SELECT * FROM customers WHERE name LIKE 'John%';

SELECT * FROM products WHERE sku LIKE 'PROD-____-2024';

-- Case-insensitive (PostgreSQL)

SELECT * FROM customers WHERE name ILIKE 'john%';

-- REGEX (PostgreSQL)

SELECT * FROM emails WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$';

-- REGEX (MySQL)

SELECT * FROM products WHERE sku REGEXP '^[A-Z]{3}-[0-9]{4}$';

-- Escaping special characters

SELECT * FROM data WHERE value LIKE '50\%' ESCAPE '\';

LIKE uses % (any characters) and _ (one character) wildcards

LIKE is case-insensitive in MySQL, case-sensitive in PostgreSQL

ILIKE provides case-insensitive matching in PostgreSQL

REGEX syntax varies by database (REGEXP, ~, REGEXP_LIKE)

Escape special characters with backslash or ESCAPE clause

Leading % wildcard prevents index usage

LIKE case-sensitive, ILIKE case-insensitive, ~ for REGEX

LIKE case-insensitive, REGEXP for regex

LIKE case-insensitive (depends on collation), no native regex

Core references in this topic include WHERE, AND, BETWEEN. 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.

AND

Requires every condition in the boolean expression to evaluate to TRUE.

condition_a AND condition_b

AND has higher precedence than OR.

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

LIKE

Pattern-matching operator for wildcard string searches.

name LIKE 'Joh%'

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

ROWS / RANGE

Defines how a window frame is sliced around the current row.