What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY has been applied. WHERE cannot use aggregate functions like COUNT(*) — use HAVING for that.

Which SQL clauses are mandatory?

Only SELECT and FROM are required in every SQL query. WHERE, GROUP BY, HAVING, and ORDER BY are all optional and used when you need their specific functionality.

Lesson 1 · Fundamentals

The BIG 6 SQL Clauses

Q: What are the 6 main SQL clauses?

The 6 main SQL clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. SELECT and FROM are mandatory in every query; the others are optional.

Q: What is the correct order of SQL clauses?

The correct order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY. A helpful memory trick: 'What? Where? Filter rows → Group → Filter groups → Sort.'

📖 5 clauses🛠 Interactive builder📝 6-question quiz⏱ ~15 min

01SELECT + FROM

The core — always required

You walk into a restaurant and say: "I want pizza from the menu." That's exactly what SQL does.

What it does

SELECT = what columns you want

FROM = which table to get it from

Real-life example

"Show me student names from the school database"

🍕 Restaurant analogy

SQL
SELECT pizza
FROM menu;

🏫 Real database

SQL
SELECT name
FROM students;

02WHERE

Filter rows — optional

Now you add a condition: "I only want veg pizza." WHERE filters rows — only rows matching your condition are returned.

What it does

WHERE = apply a condition to rows

Only rows matching the condition come through

Real-life example

"Show students who are in class 10"

🍕 Restaurant analogy

SQL
SELECT pizza
FROM menu
WHERE type = 'veg';

🏫 Real database

SQL
SELECT name
FROM students
WHERE class = 10;

03GROUP BY

Cluster rows — optional

You're analyzing data now. "Group pizzas by type and count them." GROUP BY bundles similar rows together.

What it does

GROUP BY = group similar rows together

Usually paired with COUNT, SUM, AVG…

Real-life example

"Count how many students are in each class"

🍕 Restaurant analogy

SQL
SELECT type, COUNT(*)
FROM menu
GROUP BY type;

🏫 Real database

SQL
SELECT class, COUNT(*)
FROM students
GROUP BY class;

04HAVING

Filter groups — only with GROUP BY

You want only specific groups: "Show pizza types with more than 5 items." HAVING filters groups, not individual rows.

What it does

HAVING = filter after grouping

Like WHERE, but for groups (aggregates)

Real-life example

"Show only classes with more than 30 students"

🍕 Restaurant analogy

SQL
SELECT type, COUNT(*)
FROM menu
GROUP BY type
HAVING COUNT(*) > 5;

🏫 Real database

SQL
SELECT class, COUNT(*)
FROM students
GROUP BY class
HAVING COUNT(*) > 30;

05ORDER BY

Sort results — always last

Finally, arrange results nicely: "Show pizzas sorted by price, cheapest first." ORDER BY sorts your output.

What it does

ORDER BY = sort results by a column

ASC = low→high (default), DESC = high→low

Real-life example

"Show students sorted by marks, highest first"

🍕 Restaurant analogy

SQL
SELECT pizza, price
FROM menu
ORDER BY price;

🏫 Real database

SQL
SELECT name, marks
FROM students
ORDER BY marks DESC;

🧠 Quick Memory Trick

Always remember this order — click any clause to highlight it

→→→→→

SELECT

What?

FROM

Where?

WHERE

Condition?

GROUP BY

Group?

HAVING

Filter groups?

ORDER BY

Sort?

💡 One Full Real-Life Query

"Show total students in each class — only classes with 30+ students, sorted by total"

SELECTclass name and student count

FROMthe students table

GROUP BYgroup rows by class

HAVINGonly classes with 30+ students

ORDER BYsort by student count

SQL
SELECT class, COUNT(*)
FROM students
GROUP BY class
HAVING COUNT(*) > 30
ORDER BY COUNT(*);

class	COUNT(*)
10	35
11	31

✓ 2 rows — classes 9 and 12 were filtered out by HAVING

SQL Fundamentals

What Are SQL Clauses?

SQL clauses are the building blocks of a SQL query. They define what data to retrieve, from which table, how to filter, group, and sort results. Each SQL clause hands the database engine one precise instruction — together, they form the complete SQL query structure that every developer, analyst, and data engineer relies on daily. Mastering all six SQL clauses, and the strict order they follow, is the single most important foundation any beginner can build.

SELECT — choose which columns to return
FROM — choose which table to read from
WHERE — filter rows before grouping
GROUP BY — group rows by a shared value
HAVING — filter grouped data after aggregation
ORDER BY — sort the final results

SQL Clauses Explained

A complete SQL query is built from up to six SQL clauses, each controlling a different part of the SQL query structure. The core pattern — SQL SELECT FROM WHERE — covers the three most common clauses; the remaining three handle grouping, filtering groups, and sorting. Two SQL clauses are mandatory in every query; the rest are added only when their specific behaviour is needed.

SELECT Required

Decides which columns appear in the output. You can list specific column names, wrap them in functions like COUNT() or AVG(), or use * to return every column. Without SELECT, there is no query.

FROM Required

Names the table the query reads from. Every query needs to know where its data lives. FROM points to one table (or multiple, when using JOINs). It is the foundation everything else in the query builds on.

WHERE

Filters rows before any grouping happens. Only rows that pass the condition survive — for example, WHERE status = 'active'. Works on individual row values; cannot reference aggregate functions like COUNT().

GROUP BY

Collapses rows into groups by a shared value. When you want a count, total, or average per category, GROUP BY creates those buckets. Every distinct value becomes its own group for aggregate functions to summarise.

HAVING

Filters groups after aggregation is complete. WHERE cannot touch aggregate results — HAVING can. After GROUP BY computes counts or sums, HAVING keeps only groups that meet a threshold, like HAVING COUNT(*) > 10.

ORDER BY

Sorts the final result set. Without ORDER BY, SQL makes no guarantee about row order. Sort alphabetically, numerically, or by date — ascending (ASC, the default) or descending (DESC). See the full ORDER BY lesson.

Order of SQL Clauses

SQL syntax is not free-form. The parser expects SQL clauses in a fixed sequence, and placing them out of order produces a syntax error. Understanding the correct order of SQL clauses is fundamental to writing valid SQL query structure — it is one of the first things every SQL basics tutorial must cover:

SELECT→FROM→WHERE→GROUP BY→HAVING→ORDER BY

Memory trick: "What columns? → From which table? → Filter rows → Group them → Filter groups → Sort output." Or use the initialism S–F–W–G–H–O — Some Friends Were Given Heavy Orders.

Worth noting: execution order differs from write order. The database engine processes FROM first, then WHERE, GROUP BY, HAVING, SELECT, and finally ORDER BY. This is why you cannot reference a SELECT alias inside a WHERE clause — the alias does not exist yet at the point WHERE runs.

SQL WHERE vs HAVING Difference

Both SQL clauses filter data, but they operate at completely different stages of query execution. Confusing them is among the most common SQL syntax mistakes beginners make — and understanding the difference is a core part of SQL basics that unlocks correct use of the full SQL query structure.

WHERE — Row-level filter

Runs before GROUP BY. Evaluates each row individually and discards rows that do not match — for example, WHERE country = 'US'. Cannot use aggregate functions like COUNT() or SUM() because aggregation has not happened yet.

HAVING — Group-level filter

Runs after GROUP BY. Evaluates each group and discards those that do not pass — for example, HAVING COUNT(*) > 5. Always paired with GROUP BY. You can use both WHERE and HAVING in the same query: WHERE trims rows first, HAVING trims the resulting groups.

Quick rule of thumb: if the condition references a raw column value, use WHERE. If it references an aggregate result, use HAVING.

SQL Query Structure Explained — Real-World Example

The best way to understand SQL query structure is to trace all six SQL clauses through a single realistic example. The SQL syntax below uses an orders table. Goal: find every US customer who has placed more than three completed orders, ranked by total spend.

SQL — all six clauses in one query
SELECT   customer_id,
         COUNT(*) AS order_count,
         SUM(total_amount) AS total_spend
FROM     orders
WHERE    country = 'US'
       AND  status  = 'completed'
GROUP BY customer_id
HAVING   COUNT(*) > 3
ORDER BY total_spend DESC

Trace it clause by clause: FROM loads the orders table. WHERE keeps only US completed orders. GROUP BY collapses the surviving rows into one row per customer. HAVING removes customers with three or fewer orders. SELECT picks the three columns to display. ORDER BY puts the biggest spenders at the top. All six clauses, each doing exactly one job.

Why SQL Clauses Are the Core of SQL Basics

SQL is the most widely used data language in the world, and SQL clauses are its grammar. Every analytics dashboard, back-end API, and data pipeline is powered by queries built from these same six SQL clauses. They are SQL basics — the starting point for every beginner.

SQL syntax is consistent across every dialect. Whether you write PostgreSQL, MySQL, SQLite, BigQuery, or Snowflake — these six SQL clauses exist in all of them with identical SQL syntax. Learn once, apply everywhere.
They unlock every advanced concept. Window functions, subqueries, CTEs, and JOINs all build on the SQL SELECT FROM WHERE foundation. Solid SQL basics produce solid advanced queries.
SQL interviews test them directly. Most SQL interview problems reduce to: can you GROUP BY correctly, can you distinguish WHERE from HAVING, and can you ORDER BY the right column?
Readable SQL query structure saves teams hours. A query written with correct, consistent SQL clause order is immediately understandable to any engineer. Mastering SQL query structure is mastering how to communicate with data clearly.

Frequently Asked Questions

What are the 6 main SQL clauses?

The 6 main SQL clauses are SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY. SELECT and FROM are the only mandatory ones — the rest are optional and added when you need their specific functionality.

What is the correct order of SQL clauses?

The correct writing order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY. A quick memory trick: "What columns? → From which table? → Filter rows → Group rows → Filter groups → Sort output."

What is the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping. HAVING filters groups after GROUP BY. You cannot use aggregate functions like COUNT(*) in a WHERE clause — use HAVING for that.

Can I practise these queries somewhere?

Yes — head to the SQL Playground to run real queries against our 8-table database, or try the guided SQL Challenges.

Up next

SELECT DISTINCT

Learn how to remove duplicates and find unique values in your data.

Next lesson

SELECT DISTINCT

→