SQL Tutorial 4: Filtering and Sorting Data in SQL

A table without a filter is a filing cabinet dumped on the floor. You can sort the chaotic pile, or you can even count the individual records by hand. You still do not have “paid East orders from last week.” That sentence has conditions. SQL’s job is to turn those conditions into a WHERE clause you could defend in a meeting.

This tutorial covers essential filtering and sorting techniques for our SQL series on Analytics Made Simple. We filter with WHERE clauses, combine logic with AND and OR, use IN and BETWEEN for clean sets and ranges, treat LIKE with care, sort with ORDER BY, and handle NULL values with confidence. Practice tables remain our reliable customers and orders sample datasets. Hub: SQL series.

What you will learn

• How WHERE keeps only the rows that match a condition.
• How AND and OR combine conditions (and when parentheses save you).
• How IN and BETWEEN express sets and ranges cleanly.
• How LIKE works, and why leading wildcards can hurt.
• How ORDER BY sorts results, including multi-column sorts.
• How NULL represents unknown data, and why = NULL fails.

WHERE: keeping the rows that matter

The WHERE clause sits after FROM (and after JOIN when we reach multi-table queries). It evaluates a condition per candidate row. Rows that fail drop out of the result.

SELECT
  order_id,
  customer_id,
  amount,
  status
FROM orders
WHERE status = 'paid'
ORDER BY order_id;

Example output:

Comparison operators you will use constantly:

Text comparisons are exact unless you deliberately use pattern tools. The text string 'East' is completely different from 'east' on case-sensitive collations, so keep your seed data and filters consistent.

AND and OR: combining conditions

The AND operator requires every condition to be true, while the OR operator requires at least one condition to be true.

SELECT
  order_id,
  amount,
  status
FROM orders
WHERE status = 'paid'
  AND amount >= 100
ORDER BY amount DESC;

The order must be both paid and large, meaning both conditions must evaluate to true simultaneously.

SELECT
  order_id,
  status
FROM orders
WHERE status = 'pending'
   OR status = 'cancelled'
ORDER BY order_id;

That OR pair is much clearer as an IN list (see next section). Still useful to see OR written out.

Parentheses are not optional when logic mixes

Human language is sloppy: “paid orders over 100 or pending.” Does “over 100” apply only to paid, or to both? SQL will not read your mind. Always write explicit parentheses around grouped logic.

SELECT
  order_id,
  amount,
  status
FROM orders
WHERE (status = 'paid' AND amount >= 100)
   OR status = 'pending'
ORDER BY order_id;

When in doubt, parenthesize and add a one-line comment in your notes about the business meaning.

IN: membership in a set

IN tests whether a value appears in a list.

SELECT
  order_id,
  status
FROM orders
WHERE status IN ('pending', 'cancelled')
ORDER BY order_id;

Readable, easy to extend, harder to mess up than a chain of ORs. NOT IN exists too, but watch NULL behavior in advanced cases. For clean lists of known codes, IN is a workhorse.

BETWEEN: inclusive ranges

BETWEEN is inclusive on both ends for the values you give.

SELECT
  order_id,
  order_date,
  amount
FROM orders
WHERE order_date BETWEEN '2026-01-10' AND '2026-01-20'
ORDER BY order_date;

For numbers:

SELECT
  order_id,
  amount
FROM orders
WHERE amount BETWEEN 50 AND 100
ORDER BY amount;

Because our toy dates are text in standard International Organization for Standardization (ISO) date format, string BETWEEN works for chronological order. Real systems should use proper date types and explicit time zones when the business cares. Quality and time topics expand in the data quality series.

LIKE: patterns, used carefully

The LIKE operator matches text patterns. % means any sequence of characters. _ means a single character. Different database engines handle pattern matching case sensitivity differently.

SELECT
  customer_id,
  name,
  email
FROM customers
WHERE email LIKE '%@example.com'
ORDER BY customer_id;

This pattern is useful for rough exploratory searches, but dangerous as a default production strategy:

• Leading wildcards like '%smith' often prevent tidy index use on large tables.
• User-facing search is usually better with dedicated search tools.
• Over-broad patterns return surprise rows. Always run a SELECT preview before you build a write on LIKE.

Prefer exact matches and coded fields when the business has them. Region should be a clean dimension, not LIKE '%East%', if you control the model.

ORDER BY: control the reading order

ORDER BY sorts the result set. Ascending is the default sort order, while DESC reverses it.

SELECT
  order_id,
  amount,
  status
FROM orders
ORDER BY amount DESC, order_id ASC;

Example output:

Multi-column sort means: break ties with the next column. That is how you get stable, readable rankings.

Remember our column selection guide: LIMIT without ORDER BY is a randomish snack. Combining a limit clause with an explicit order clause produces a predictable and repeatable sample.

NULL basics: unknown is not a value

NULL means unknown or missing. It is not zero, nor is it an empty string, even when some export tools blur them together. Comparisons with = do not work the way beginners hope.

-- This does NOT find missing emails
SELECT
  customer_id,
  name,
  email
FROM customers
WHERE email = NULL;

Use IS NULL and IS NOT NULL:

SELECT
  customer_id,
  name,
  email
FROM customers
WHERE email IS NULL
ORDER BY customer_id;

On our toy seed data, customer Frankie Ng should appear. For outreach lists:

SELECT
  name,
  email
FROM customers
WHERE email IS NOT NULL
ORDER BY name;

Aggregates later will ignore NULLs in some functions (for example AVG() skips them). That is a feature and a foot-gun. Know what your metric should do with missing values before you celebrate a number.

Worked example: a realistic filter pack

Business ask: “Paid orders in January 2026 with amount at least 50, sorted biggest first. Exclude cancelled forever; we only want paid.”

Start from the full orders picture in your head (or a quick preview query), then filter.

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  status
FROM orders
WHERE status = 'paid'
  AND order_date BETWEEN '2026-01-01' AND '2026-01-31'
  AND amount >= 50
ORDER BY amount DESC, order_id;

Example output:

Second ask: “Customers in East or West who have an email on file.”

SELECT
  customer_id,
  name,
  region,
  email
FROM customers
WHERE region IN ('East', 'West')
  AND email IS NOT NULL
ORDER BY region, name;

You now have a reusable pattern: membership set, NULL check, and a stable sort. When our upcoming joins tutorial connects these customers to orders, you will put table-specific filters where they are cheapest to think about: filter each side, then join, or filter clearly after with unambiguous column names.

Filter logic checklist before you run

• Can I say the grain of a row out loud?
• Which columns implement the business words (paid, region, date range)?
• Do I need AND, OR, or both, and did I parenthesize?
• Are codes exact ('paid') rather than fuzzy LIKE?
• Did I handle NULL explicitly when missing data matters?
• Is ORDER BY defined if I care about sequence or LIMIT?

Translate English operators carefully

Business language is full of soft edges. “Around January,” “big orders,” and “active-ish customers” are not SQL. Push for thresholds:

• “January” becomes a date range with an agreed timezone and inclusive ends.
• “Big orders” becomes amount >= 100 or another number someone owns.
• “Active” becomes a rule: paid in the last 90 days, or status in a set, or both.

If two stakeholders disagree on the threshold, that is a product or finance decision, not a place for you to invent a clever LIKE pattern. Write the rule in the ticket, then encode it in a WHERE clause. When the rule changes next quarter, you will know what to edit.

Combining filters with joins later

Our upcoming joins tutorial will connect customers and orders. The filtering skills here still apply: decide which predicates belong to which table. Geographic region filters usually target columns in the customers table. Status and amount filters hit orders. Date windows hit order_date unless your business defines activity differently. Sketch the filters on each table before you write the join so you do not discover AND/OR bugs in a 12-line ON clause at 5 p.m.

A pre-join checklist on paper is enough:

• customers: region IN (...), email IS NOT NULL if needed.
• orders: status = ‘paid’, order_date BETWEEN …
• result grain: one row per order or one row per customer after aggregate.

Common mistakes

• Confusing boolean logic between AND and OR conditions. Sketch a truth table on paper if needed, which is standard professional practice when untangling conditions.
• Filtering against the wrong column name. “East revenue” is not always a column on orders. You may need a join later.
• Using LIKE for coded dimensions. Prefer clean IN lists.
• Forgetting that range checks are completely inclusive of both boundary endpoints. Off-by-one on dates is a classic incident report.
• Writing = NULL. Use IS NULL.
• Sorting as a substitute for filtering. Putting pending rows at the bottom does not remove them from a total.
• Silent case mismatches. No rows returned often means a spelling or case issue, not an empty business.

Practice

• Write filters for paid-only, pending-only, and everything except cancelled.
• Find orders with amount between 40 and 90 inclusive.
• List customers missing email. List customers with email.
• Sort orders by date ascending, then by amount descending as a tie breaker.
• Translate three Slack questions from your work into WHERE clauses on paper, even if you cannot run them on real data yet.
• When filters reveal quality issues (mystery statuses, null-heavy columns), keep notes and visit data quality. Broader paths: Learn, Python, spreadsheets to data.

Quick recap

• WHERE filters rows; SELECT still chooses columns.
• AND narrows, OR widens; parentheses make mixed logic explicit.
• IN handles sets; BETWEEN handles inclusive ranges.
• LIKE is a careful tool, not a default for every text question.
• ORDER BY defines result order; pair it with LIMIT when sampling tops.
• NULL needs IS NULL or IS NOT NULL; unknown is not a normal value.

Next: turn many rows into answers with COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING. That is the focus of our next tutorial.

FAQ: filters and sorting

Why did my filter return zero rows?

Check spelling and case first. Then check whether the column is the one you think it is. Then check whether the values exist at all with a DISTINCT sample. Zero rows is often a logic typo, not an empty business day. Run a broader SELECT query without the new filter to confirm data is present.

Should I filter dates with LIKE?

Prefer real date comparisons or standardized date ranges you understand. LIKE '2026-01%' can work on standardized date strings in a pinch for monthly slices, but it is brittle compared with BETWEEN or proper date functions on typed columns. As your models mature, store dates as dates.

How do I sort nulls?

Engines differ on whether NULLs sort first or last. Some support NULLS FIRST or NULLS LAST. For portable learning, be aware that missing values may cluster at an end of the result. If null ordering matters for a report, test it on your engine and document the behavior.

Sources

Filtering and sorting references:

• SQLite: expressions and operators (comparisons, pattern matching, boolean logic, and nulls)
• SQLite: SELECT reference (filtering, sorting, and row limits)
• PostgreSQL: table expressions and WHERE
• PostgreSQL: comparison functions and operators
• Analytics Made Simple: SQL series
• Analytics Made Simple: Learn
• Analytics Made Simple: Data quality series
• Analytics Made Simple: Python series
• Analytics Made Simple: Spreadsheets to data

Merch on Etsy

Open store →

Standard Deviation White Ceramic Mug View on Etsy → Standard Deviation T-Shirt View on Etsy → You're On Mute Coffee Mug View on Etsy → Git Version Control Shirt View on Etsy →