Restricting and Sorting Data in SQL: WHERE and ORDER BY Explained

WHERE is how you tell SQL which rows you actually want. Without it, a query hands you back the entire table. With it, you keep only the rows that match your condition.

SELECT * FROM orders WHERE order_amount > 5000;

That query returns orders above 5,000. Everything below sits on disk untouched. It runs right after FROM and before any grouping, which makes it the earliest chance the database has to throw rows away. That is also why it is the single biggest performance lever in most real-world queries — a tight filter reduces work for everything downstream.

The conditions themselves can be anything: an exact value, a range, a pattern in text, or a NULL check. Whether you are narrowing down orders by status, customers by region, or products by category, this is the clause that does the actual cutting.

ORDER BY puts the result set in a specific sequence. You name one or more columns, and optionally give each a direction — ASC or DESC.

SELECT * FROM orders ORDER BY order_amount DESC;

Highest-value orders come first. Leave off the direction and the sort defaults to ascending, lowest to highest.

Sorting is not decoration. A list of a hundred orders sorted by amount tells you something in two seconds that the same list in arbitrary order takes two minutes to read. Reports live and die on this clause. And because ORDER BY runs after SELECT, it can see the full result — including aliases — which means you can sort by things that never appear on screen.

Ascending. If you write ORDER BY credit_limit with no direction, SQL sorts from smallest to largest.

Text goes A to Z. Numbers go smallest to largest. Dates go oldest to newest. Every major database behaves the same way, which is why you almost never see ASC written out — most developers just leave it off and let the default work.

To reverse the order you have to say so explicitly:

SELECT * FROM customers ORDER BY credit_limit DESC;

Now the biggest credit limit comes first. There is no shortcut and no ambiguity. The default is ascending, and descending requires the keyword.

Yes, and you do not need any special syntax for it. Each column in the ORDER BY list carries its own direction.

SELECT customer_name, region, credit_limit
FROM   customers
ORDER BY region ASC, credit_limit DESC;

Region sorts alphabetically. Inside each region, credit limit drops from highest to lowest. The database applies the keys left to right, which means the second one only kicks in when the first has a tie.

This is the pattern behind most ranking reports: one column to organise by, one column to rank within. Region and revenue. Product category and units sold. Department and headcount. Once you internalise it, you will write fewer queries with mental gymnastics and more queries that just do the obvious thing.

The difference is timing, and everything else follows from that.

WHERE filters rows before any grouping has happened. When you write WHERE order_amount > 5000, the database throws away every order that does not clear 5,000 before the rest of the query even sees it.

HAVING filters groups after GROUP BY has produced them. If you want customers whose average order value is above 5,000, that average does not exist yet when WHERE runs — it is computed per group later. The condition has to go in HAVING:

SELECT customer_id, AVG(order_amount) AS avg_order
FROM   orders
GROUP BY customer_id
HAVING AVG(order_amount) > 5000;

Short version: WHERE picks rows, HAVING picks groups. Try to swap them and the database stops you immediately with an error about aggregate functions not being allowed in WHERE. That error is not the database being difficult — it is pointing out that the value does not exist yet.

BETWEEN filters a column down to a range. Instead of writing two separate comparisons and joining them with AND, you write one clause that reads the way you would say it out loud.

SELECT * FROM orders WHERE order_amount BETWEEN 5000 AND 20000;

Every order between 5,000 and 20,000, inclusive of both endpoints. Exactly the same as WHERE order_amount >= 5000 AND order_amount <= 20000, just shorter.

It works with numbers, dates, and text, and it is most useful when the user thinks in ranges — prices, ages, timestamps. There is one trap worth knowing: on timestamp columns, BETWEEN '2024-01-01' AND '2024-01-31' stops at midnight on the 31st, not at the end of that day. Anything that happened during the day falls outside the range. End the range at '2024-02-01', or cast the timestamp to a date, and the problem goes away.

LIKE is how you search text when you do not know the exact value you are looking for. You give it a pattern, and it returns anything that fits.

Two wildcards do the work:

• % matches any sequence of characters, including none.
• _ matches exactly one character.

SELECT * FROM customers WHERE customer_name LIKE 'A%';

Every customer name starting with A comes back — Alice Corp in our sample. Change the pattern to '%Traders' and you find names ending in "Traders." Change it to '%a%' and you get every name that contains the letter a, anywhere.

Two caveats worth remembering. Case sensitivity depends on the database — some are strict, some are not — so verify before relying on a match. And if your pattern starts with a wildcard, like '%mart', the database cannot use a normal index and has to scan every row. That is fine on small tables but painful at scale.

IN checks whether a column matches any value from a list. It is the cleaner version of a long chain of OR conditions.

SELECT * FROM customers
WHERE region IN ('North', 'South', 'West');

Everyone in one of those three regions comes back. Written with OR, the same query would be:

SELECT * FROM customers
WHERE region = 'North'
   OR region = 'South'
   OR region = 'West';

Both work, but the IN version is easier to read and scales better when the list grows past four or five values. The list can also be a subquery — a nested SELECT that returns one column.

One thing to know: if the list contains NULL, IN will not match against it. NULL never equals anything, not even another NULL, so the comparison silently fails.

Each of the three does something different, and together they turn a single condition into a filter that actually matches what you have in mind.

• AND requires every condition to be true.
• OR requires at least one condition to be true.
• NOT flips the truth value of the condition that follows.

SELECT * FROM orders
WHERE order_status = 'Delivered' AND order_amount > 5000;

Only delivered orders above 5,000 come back. Now switch the AND to an OR and the net widens considerably:

SELECT * FROM orders
WHERE order_status = 'Delivered' OR order_status = 'Shipped';

Every delivered or shipped order. And with NOT:

SELECT * FROM orders
WHERE NOT order_status = 'Pending';

Everything that is not pending. The one thing to remember is that SQL evaluates AND before OR, just like multiplication before addition in arithmetic. When your intent mixes the two, put parentheses where you mean them. Missing parentheses are the quietest and most expensive SQL bugs in production.

No, and the reason is entirely about timing. Aggregate functions — SUM, AVG, COUNT, MIN, MAX — cannot appear in a WHERE clause.

WHERE runs second in the pipeline. It sees one row at a time. At that moment, no grouping has happened, no aggregation has happened, and the count of rows in a group simply does not exist yet. You cannot evaluate a condition against a value that has not been computed.

-- This runs fine — it filters rows
SELECT * FROM orders WHERE order_amount > 5000;

-- This fails — the average has not been computed yet
SELECT * FROM orders WHERE AVG(order_amount) > 5000;

When you actually need to filter on an aggregate, use HAVING, which runs after GROUP BY and therefore sees the grouped results:

SELECT customer_id, AVG(order_amount)
FROM   orders
GROUP BY customer_id
HAVING AVG(order_amount) > 5000;

Same wording, different clause. The database knows which stage of the pipeline to run it at, and that is the whole trick.

NULL does not compare like a normal value. It represents an unknown, and you cannot say that an unknown is greater than or less than a known number. So SQL has to make a rule for where NULLs go when you sort, and the rule differs between databases.

In Oracle and PostgreSQL the defaults are:

• ORDER BY column ASC puts NULLs last.
• ORDER BY column DESC puts NULLs first.

MySQL and SQL Server do the opposite — NULLs come first in ascending order and last in descending. This is exactly the kind of inconsistency that causes cross-database bugs.

SELECT * FROM customers
ORDER BY credit_limit ASC NULLS FIRST;

That forces NULL credit limits to the top of the result, overriding the Oracle and PostgreSQL defaults. NULLS LAST does the opposite. Use them whenever the position of NULLs actually matters in your report — which, in practice, is almost always.

It depends on the database, and there is no universal answer. The two camps are:

• Oracle and PostgreSQL: NULLs last in ascending order, first in descending.
• MySQL and SQL Server: NULLs first in ascending order, last in descending.

Under the hood, this is just a choice about whether NULL counts as the smallest possible value or the largest. Neither is wrong. The two systems simply picked different models, and both are consistent within themselves.

Because of that, any query where NULL placement matters should be explicit about it. NULLS FIRST and NULLS LAST work in Oracle and PostgreSQL. On MySQL and SQL Server, you sort a computed column instead — something like ISNULL(credit_limit, 999999) — to force the placement you want.

In Oracle and PostgreSQL, you attach the keyword directly to the column inside ORDER BY.

SELECT * FROM customers
ORDER BY credit_limit ASC NULLS LAST;

NULL credit limits go to the bottom. Flip the keyword and they go to the top:

SELECT * FROM customers
ORDER BY credit_limit ASC NULLS FIRST;

Both keywords work independently of the direction — DESC NULLS FIRST sorts high to low and puts NULLs at the top, which is what you want when you are hunting for incomplete records rather than hiding them.

MySQL and SQL Server do not support these keywords. There you sort a computed column instead. On SQL Server, ORDER BY ISNULL(credit_limit, 1) gets NULLs to the bottom. On MySQL, ORDER BY credit_limit IS NULL, credit_limit does the same thing. Different mechanics, same result.

Yes, and it comes up more than you would expect once you are working with real data. Optional fields — a credit limit, a bonus, a middle name — regularly contain NULLs, and reports still need to sort by them.

SELECT customer_id, customer_name, credit_limit
FROM   customers
ORDER BY credit_limit ASC, customer_name ASC;

The database sorts by credit limit first. Customers with a value come in ascending order of that value. Customers with a NULL limit are grouped together wherever the default rule for the database places them — Oracle and PostgreSQL put them at the bottom, MySQL and SQL Server at the top.

Where ties happen — including between all the NULL rows — the second column (customer_name) breaks the tie alphabetically. That gives you a fully deterministic result with no ambiguity left on the table.

If you want NULLs at the bottom regardless of which database you are on:

SELECT * FROM customers
ORDER BY credit_limit ASC NULLS LAST, customer_name ASC;

Now the list is sorted primarily by credit limit, secondarily by name, and every customer with an unknown credit limit sits at the end where nobody has to trip over them.

That works fine. You can sort by any column in the source tables, whether or not it appears in the SELECT list.

The reason is the execution order. ORDER BY runs after SELECT, and by that point the database still has access to every original column plus the ones you computed. So the output can be narrower than what the sort uses.

SELECT customer_id, customer_name
FROM   customers
ORDER BY created_on ASC;

The result shows customer id and name only, but the order follows created_on, which never appears on screen. That gives you a clean report without giving up the ordering the reader expects.

This pattern shows up all the time in real reporting. The reader wants names and ids. You want the sort to reflect when the customer was created, or their most recent order, or something else that would just be noise in the output. Nothing forces you to expose the sort column.

SELECT *
FROM   customers
ORDER BY region ASC NULLS LAST;

Every customer comes back, sorted by region alphabetically, with any rows whose region is NULL pushed to the end.

Now, on Oracle and PostgreSQL, that explicit clause happens to match the default — ascending order already puts NULLs last. So why write it? Portability, mainly. On MySQL and SQL Server, the default puts NULLs first, and without the explicit clause the query would produce a very different report. Writing NULLS LAST makes the intent visible and the behaviour predictable, no matter which engine eventually runs the query.

This is the shape you would use for any report where customers are grouped by region and the unassigned ones are deliberately listed at the end.

SELECT order_id, customer_id, order_amount, order_date
FROM   orders
WHERE  order_amount > 5000
ORDER BY order_date ASC NULLS LAST, order_amount DESC;

The query has three moving parts.

• WHERE order_amount > 5000 drops every order below 5,000 before anything else happens. Only the meaningful ones survive.
• ORDER BY order_date ASC NULLS LAST sorts the surviving rows oldest to newest. Orders with no date are pushed to the bottom so they do not crowd the top of the report.
• order_amount DESC handles ties. When two orders share a date, the larger one comes first.

This is the shape of a real report. The WHERE clause reduces the noise. The ORDER BY puts the useful information at the top. The tie-breaker decides what happens when the primary sort key is not unique — which, in real data, is almost always.

SELECT *
FROM   products
WHERE  price BETWEEN 50 AND 500
ORDER BY price DESC;

The WHERE clause keeps only products with a price in that range, inclusive of both endpoints. The ORDER BY then sorts them from most expensive to least, so the top of the list is the most valuable product in the range.

Two clauses, two jobs. Filter the range, then order it so the reader gets what they actually wanted without having to scroll through noise. It is the same pattern behind any ranked-range report — top products by revenue, top customers by spend, top students by score. Once you have the pattern, the queries write themselves.

Both organise data, but they do completely different things to it.

GROUP BY merges rows that share the same values in the columns you name. It reduces the result — you start with many rows and end up with one per group.

SELECT region, SUM(credit_limit) AS total_credit
FROM   customers
GROUP BY region;

That returns one row per region, with the total credit limit for each. The individual customers disappear into their region groups.

ORDER BY sorts rows. It does not reduce them.

SELECT region, SUM(credit_limit) AS total_credit
FROM   customers
GROUP BY region
ORDER BY total_credit DESC;

Now the same groups come back, but sorted with the biggest total at the top. GROUP BY shapes the result. ORDER BY presents it. When you see them together in a query, that is exactly what they are doing — one reshaping, the other sequencing.

A useful rule of thumb: if you are summarising, you want GROUP BY. If you are just rearranging what is already there, you want ORDER BY.

COUNT() returns a number: the number of rows in a result, or the number of non-NULL values in a column.

SELECT COUNT(*) FROM orders;

Every row in the orders table is counted, NULLs included. On our sample data, that returns 7.

SELECT COUNT(credit_limit) FROM customers WHERE region = 'North';

That counts North-region customers whose credit_limit column is not NULL. If Fiona Mart were in the North, she would not be counted — she has no credit limit on file. This is the difference between COUNT(*) and COUNT(column): the wildcard counts rows, the column counts values.

COUNT pairs most often with GROUP BY, where it tells you how many records are in each group.

SELECT region, COUNT(*) AS customer_count
FROM   customers
GROUP BY region;

One row per region, with the number of customers in each. Combine that with HAVING and you can filter groups by size — show me only the regions with more than two customers, and so on.

The single biggest win is an index on the columns you sort by. If the index already stores rows in the order you want, the database can walk through it in sequence and skip the sort step entirely.

CREATE INDEX idx_customers_credit ON customers (credit_limit);

After that index exists, SELECT * FROM customers ORDER BY credit_limit runs from the index directly. The database does not have to load every row and sort them in memory. That difference is often the gap between a query that takes a second and one that takes forty.

Three other habits worth building:

• Filter before you sort. A tight WHERE clause reduces the number of rows that have to be ordered. Sorting ten rows is free. Sorting ten million is not.
• Limit the result if you can. If the report only needs the top ten, add LIMIT 10. The database can stop after finding ten matching rows instead of ordering the full result set.
• Match the index order to the sort order. If you frequently sort by region, credit_limit, build the index in that exact order. A single-column index on credit_limit will not help once region is the primary key.

None of these are advanced tricks. They are the everyday moves that separate a fast report from a slow one. When a query that used to run in a second suddenly takes a minute, ORDER BY is the first place to look.

Knowing the difference matters because they do completely different things, and confusing them produces either the wrong result or a query that is far more complicated than it needs to be.

ORDER BY sets the sequence of the rows in the result. It does not change what is in the result — just the order. Every row you started with is still there.

GROUP BY merges rows that share values in the columns you name. It is almost always paired with an aggregate function — SUM, AVG, COUNT — because its purpose is to summarise. You start with many rows and end up with one per group.

-- ORDER BY: same 7 customers, reordered
SELECT customer_name, credit_limit FROM customers ORDER BY credit_limit DESC;

-- GROUP BY: 3 regions, credit limits collapsed into totals
SELECT region, SUM(credit_limit) FROM customers GROUP BY region;

When you see both in the same query, they are working together. GROUP BY decides what shape the result takes. ORDER BY decides how it is presented. Understanding which is doing what is most of what separates someone who writes SQL from someone who writes SQL well.

NULL gets special treatment in ORDER BY because it is not a value in the ordinary sense. It represents missing information, and missing information cannot be compared.

In Oracle and PostgreSQL:

• Sorting ASC — NULLs come last.
• Sorting DESC — NULLs come first.

MySQL and SQL Server flip both of those. That alone is a reason to be explicit about NULL placement in any query that might run on more than one database.

SELECT * FROM customers
ORDER BY credit_limit ASC NULLS LAST;

That forces NULL credit limits to the bottom no matter the engine. Swap to NULLS FIRST when you want them at the top — for example, when you are running a data-quality report and want to see all the incomplete records immediately.

The bigger point is that missing data is common in real systems. Handling it consistently in reports is part of writing SQL that other people can trust.

This is a common point of confusion, and the confusion comes from what DISTINCT actually removes. It removes duplicate rows — not duplicate values from one column while keeping others.

SELECT DISTINCT region FROM customers;

That returns a unique list of regions. If three customers share the North region, North appears once. Nothing else is in the output. Just the unique regions.

Add a second column and de-duplication applies to the pair:

SELECT DISTINCT region, credit_limit FROM customers;

Two rows are only considered duplicates if both region and credit_limit match. Two customers in the same region with different credit limits both come back, because the pair is different.

If what you actually want is one row per region with a value from another column — say, the count of customers — reach for GROUP BY:

SELECT region, COUNT(*) AS customer_count
FROM   customers
GROUP BY region;

One row per region, with the customer count for each. That is the correct tool when your goal is one row per key rather than de-duplication. DISTINCT and GROUP BY look similar in simple cases but solve different problems.