Branch logic inside your queries

SQL has two core tools for branching logic. OR branches in the WHERE clause to decide which rows to keep. CASE WHEN branches in SELECT to decide what value each row should produce.

The OR operator returns rows that satisfy at least one of multiple conditions. Unlike AND, which requires all conditions to be true, OR is inclusive. A row passes the filter if any single condition evaluates to true.

Each condition is checked in sequence. If any evaluates to true, the row is included. When you have many OR conditions on the same column, the IN operator is a cleaner shorthand.

While OR decides which rows to include, CASE WHEN decides what value to produce for each row. It evaluates conditions top to bottom and returns the value for the first match:

The CASE expression checks each WHEN condition in order. The first true condition determines the result. If none match, ELSE provides a default. Without ELSE, unmatched rows return NULL.

Both OR and CASE WHEN evaluate conditions, but they serve different purposes:

Try using CASE WHEN to classify rows based on a condition.

SELECT
  order_id,
  amount,
  ___
    ___ amount >= 500 THEN 'high'
    ELSE 'low'
  END AS priority
FROM orders

OR is fundamentally different from AND: it expands your result set rather than narrowing it, so a single true condition is enough to keep a row.

CASE WHEN is one of the most versatile tools in SQL because it lets you embed conditional logic directly inside a SELECT list without filtering any rows out.

When you have many OR conditions on the same column, replacing them with an IN list makes the query significantly easier to read and extend.

Control how filter conditions evaluate

• Comparison operators (=, <, >, IN) • Logical NOT • Logical AND • Logical OR

The animation below shows how SQL actually evaluates this WHERE clause. Notice the order of operations: comparisons first, then the AND, then the OR.

Without parentheses, this expression evaluates as: (country = 'US' AND status = 'active') OR (total_spent > 1000)

The AND executes before OR. As a result, it returns both active U.S. users and any users (in any country) who have spent more than 1000.

Now the parentheses force SQL to evaluate the OR condition first. The query returns only users in the U.S. who are either active or high spenders. This difference in parentheses changes the meaning, and the result set, completely.

The database evaluates each group independently, then combines them using OR.

Explicit Grouping

Parentheses override default precedence rules

Readability

Prevent logical ambiguity for future readers

Nested Evaluation

Innermost parentheses are evaluated first

Don't Overdo It

Use parentheses for clarity, not clutter

SELECT
  order_id,
  country,
  total
FROM orders
WHERE (
  country = 'US'
  ___ country = 'CA'
)
___ total > 500

The AND-before-OR precedence rule trips up experienced engineers regularly. Treating parentheses as mandatory whenever you mix AND and OR is the safest habit to develop.

Search text with wildcard patterns

When you only know part of a value, such as the prefix of an email, the substring of a URL, or the final digits of an ID, LIKE lets you match flexible patterns without writing multiple OR clauses.

LIKE supports two wildcards: % matches zero or more characters, and _ matches exactly one character. Combine them to target just the pieces you care about.

Common LIKE patterns:

The power of LIKE comes from wildcards that match variable portions of text.

You can invert the logic with NOT LIKE, or add multiple patterns using OR/AND for more explicit control.

Pattern matching with LIKE is one of the most practical filtering techniques in everyday SQL.

SELECT
  product_id,
  product_name
FROM products
WHERE product_name ___ ___

LIKE is case-sensitive by default in most databases. Wrap both sides in LOWER() for case-insensitive matching.

Return results one page at a time

Large tables can return millions of rows. LIMIT keeps results manageable and queries performant.

Production tables regularly hold millions of rows, but dashboards rarely need the full data set. LIMIT lets you cap how many rows return, keeping result sets predictable and fast.

LIMIT is typically paired with ORDER BY so the "top" rows are deterministic. Otherwise you may get a different subset on each run.

Here we sort by the metric we care about, then keep only the first five rows. LIMIT always goes at the end of the statement.

OFFSET lets you skip rows, enabling pagination through results page by page.

Pair LIMIT with OFFSET to skip rows and paginate through result pages:

Controlling result size with LIMIT is essential for both performance and usability.

SELECT
  product_name,
  price
FROM products
ORDER BY price ___
LIMIT ___

Always pair LIMIT with ORDER BY to get deterministic results. Without ORDER BY, the rows returned by LIMIT are unpredictable.

OFFSET skips rows before starting to return results, enabling pagination through large datasets page by page.

Large OFFSET values can be slow because the database must still process all skipped rows internally.

Select rows within a value range

BETWEEN provides a clean syntax for filtering values within an inclusive range.

BETWEEN expresses inclusive ranges. It translates to "greater than or equal to the lower bound AND less than or equal to the upper bound." It works with numbers, dates, and even text (alphabetical ranges).

BETWEEN works seamlessly with AND to filter on multiple range criteria simultaneously.

This query narrows the data set to Q1 orders whose totals fall within a band. Stacking BETWEEN with AND combines date and numeric ranges cleanly.

SELECT
  order_id,
  order_date,
  total
FROM orders
WHERE order_date ___ '2024-01-01'
___ '2024-03-31'

BETWEEN provides the most readable syntax for inclusive range queries and communicates intent clearly to anyone reviewing the code later.

Stacking multiple BETWEEN conditions with AND is a clean pattern for filtering data across several dimensions simultaneously, such as date range and value range together.