Understanding When to Use “WHERE”, “ORDER BY”, and “LIMIT” in SQL

[Rajeev Bagra]

When learning SQL, one of the most common points of confusion is understanding the roles of “WHERE”, “ORDER BY”, and “LIMIT”. Beginners often try to use these clauses interchangeably, but each one serves a very specific purpose in a query.

Understanding how these clauses work—and in what order SQL processes them—will make it much easier to write correct queries.

---

The Purpose of “WHERE”

The “WHERE” clause is used to filter rows based on a condition.

It tells the database:

«“Only return rows that satisfy this condition.”»

Example:

SELECT name
FROM districts
WHERE state = ‘Massachusetts’;

This query returns only districts located in Massachusetts.

Without the “WHERE” clause, SQL would return all districts from all states.

Key idea

“WHERE” reduces the dataset before any sorting happens.

---

The Purpose of “ORDER BY”

The “ORDER BY” clause is used to sort the results.

Example:

SELECT name
FROM districts
ORDER BY name;

This query sorts districts alphabetically.

Sorting can also be done on numerical columns.

Example:

SELECT districts.name
FROM districts
JOIN expenditures
ON districts.id = expenditures.district_id
ORDER BY expenditures.per_pupil_expenditure DESC;

Here, the districts are sorted by highest per-pupil expenditure first.

Sorting directions

Keyword| Meaning
“ASC”| Ascending order (default)
“DESC”| Descending order

Example:

ORDER BY expenditures.per_pupil_expenditure DESC

This places largest values first.

---

The Purpose of “LIMIT”

The “LIMIT” clause restricts how many rows are returned.

Example:

SELECT name
FROM districts
LIMIT 10;

This returns only the first 10 rows.

“LIMIT” is commonly used together with sorting to get results like:

• Top 10
• Lowest 5
• Most expensive items

Example:

SELECT districts.name
FROM districts
JOIN expenditures
ON districts.id = expenditures.district_id
ORDER BY expenditures.per_pupil_expenditure DESC
LIMIT 10;

This query returns the 10 districts with the highest per-pupil spending.

---

Why “WHERE” Is Not Needed in This Case

If the goal is simply:

«Show the top 10 districts by expenditure»

No filtering condition is required.

The query only needs to:

1. Join the tables
2. Sort expenditures
3. Return the first 10 rows

Therefore the query becomes:

SELECT districts.name
FROM districts
JOIN expenditures
ON districts.id = expenditures.district_id
ORDER BY expenditures.per_pupil_expenditure DESC
LIMIT 10;

---

The Order SQL Executes Clauses

Another reason beginners get confused is because SQL does not execute queries in the order we write them.

The database actually processes them roughly like this:

Step| Clause
1| “FROM”
2| “JOIN”
3| “WHERE”
4| “ORDER BY”
5| “LIMIT”

This means:

1. Tables are selected.
2. Tables are joined.
3. Rows are filtered.
4. Results are sorted.
5. The final number of rows is limited.

Understanding this execution order helps explain why sorting cannot be placed inside “WHERE”.

---

A Simple Rule to Remember

Goal| SQL Clause
Filter rows| “WHERE”
Sort results| “ORDER BY”
Restrict number of rows| “LIMIT”

If you remember this rule, writing SQL queries becomes far easier.

---

Final Thought

Many SQL mistakes come from trying to use the wrong clause for a task—for example, attempting to sort data using “WHERE”.

Instead, think of a query as a pipeline:

1. Get the data
2. Filter it (if necessary)
3. Sort it
4. Show only the rows you need

Once you start thinking in this sequence, SQL queries become much easier to design and debug.

[Author]

Posts