SQL Server
Understanding the Parts of a SELECT Statement in SQL Server
What each clause of a T-SQL SELECT statement does, which parts are required and which are optional, and how one complete query reads from SELECT to ORDER BY.
On this page
In the previous lesson you wrote your first queries: SELECT some columns FROM a table, maybe with a WHERE and an ORDER BY. Those queries already used most of the building blocks of T-SQL. They just did not use all of them at once.
This lesson, the fourth step of SQL Server Foundations, takes the SELECT statement apart. You will see the seven clauses you will meet in almost every query you ever read, what each one is responsible for, which ones are required, and how they fit together in one complete example on the practice table dbo.Customers.
A SELECT statement is a list of clauses
A clause is one part of a statement that starts with a keyword and does one job. FROM dbo.Customers is a clause. WHERE State = 'FL' is another. A SELECT statement is a sequence of clauses written in a fixed order:
SELECT -- which columns or values to return
FROM -- where the rows come from
JOIN -- other tables to combine with (optional)
WHERE -- which rows to keep (optional)
GROUP BY -- how to group rows (optional)
HAVING -- which groups to keep (optional)
ORDER BY -- how to sort the final result (optional)That block is not a runnable query; it is the skeleton. You never have to use every clause, but when you use several, they must appear in this order. Writing WHERE before FROM, or ORDER BY before GROUP BY, is a syntax error.
- RequiredSELECT
- Columns and output
- Usual startFROM
- Source table
- OptionalJOIN
- Combine tables
- OptionalWHERE
- Filter rows
- OptionalGROUP BY
- Create groups
- OptionalHAVING
- Filter groups
- OptionalORDER BY
- Sort final result
The required parts: SELECT and FROM
SELECT says what the result should contain. Usually that is a list of columns, but it can also be calculated values, such as FirstName + ' ' + LastName, or summaries such as COUNT(*). Each item in the list becomes one column of the result. You can name an item with AS, which is called a column alias:
SELECT
CustomerId,
FirstName + ' ' + LastName AS FullName
FROM dbo.Customers;FROM says where the rows come from: a table such as dbo.Customers, a view, or later in this series, several tables joined together.
Strictly speaking, only SELECT is required. SELECT 1 + 1; or SELECT GETDATE(); run without a FROM, because they do not read any table. They are handy for quick checks. But as soon as you want data that lives in a table, you need FROM, so in practice every query you write for real work starts with these two clauses.
The optional clauses
Everything after FROM is optional. Each clause is added only when the question needs it.
JOIN brings rows from a second table into the query, matched on a column the two tables share. For example, an orders table joined to customers on CustomerId lets one result show both the order and the customer’s name. Our practice database has only one table so far, so there is no join in this lesson. Joins get their own stage of the path.
WHERE filters rows. Each row is tested against the condition, and only rows for which it is true are kept: WHERE State = 'FL' keeps Florida customers and drops everyone else. The WHERE lesson covers it in depth.
GROUP BY turns many rows into groups. GROUP BY State puts all Florida customers in one group, all Texas customers in another, and so on. The result then has one row per group, not one row per customer. Grouping is almost always combined with an aggregate function such as COUNT(*), SUM or AVG, which calculates one value per group.
HAVING filters groups, the way WHERE filters rows. HAVING COUNT(*) > 1 keeps only the groups that contain more than one row. It only makes sense together with grouping or aggregates.
ORDER BY sorts the final result. Without it, SQL Server returns rows in whatever order is cheapest for it, and that order can change from one run to the next. If the order matters to whoever reads the result, say so with ORDER BY.
One complete query
Here is a query that uses every clause except JOIN. It answers a real question: which states have more than one customer, and how many customers does each have?
SELECT
State,
COUNT(*) AS CustomerCount
FROM dbo.Customers
WHERE State IS NOT NULL
GROUP BY State
HAVING COUNT(*) > 1
ORDER BY CustomerCount DESC;On the ten customers in JaviBuildsLab it returns two rows:
| State | CustomerCount |
|---|---|
| FL | 3 |
| TX | 2 |
Walk through it clause by clause:
SELECT State, COUNT(*) AS CustomerCountasks for two output columns: the state code, and the number of rows in each group, namedCustomerCount.FROM dbo.Customersreads the practice table.WHERE State IS NOT NULLdrops any customer without a state before anything is grouped. Every customer in our table has a state, so it removes nothing today, but it protects the result if a row without a state is ever added. (IS NOT NULLis the correct way to test for a missing value;= NULLdoes not work, as the WHERE lesson explains.)GROUP BY Statebuilds one group per state: three customers in FL, two in TX, one each in WA, CO, OR, MA and IL.HAVING COUNT(*) > 1keeps only groups with more than one customer: FL and TX.ORDER BY CustomerCount DESCsorts the result from the largest count to the smallest.DESCmeans descending; the default,ASC, is ascending.
Notice how each clause narrows or reshapes what the previous one produced. That chain is the heart of the next lesson, which shows that SQL Server does not evaluate the clauses in the order you write them.
Simple queries and advanced queries use the same clauses
A simple query and a query someone spent a day writing are made of the same parts. The difference is how many clauses they use and how much each clause does.
The query from lesson 3 uses three clauses:
SELECT
CustomerId,
FirstName,
LastName
FROM dbo.Customers
WHERE State = 'FL';The query above uses six. A production report might add several joins, a longer WHERE with many conditions, and more aggregates, but it still reads as SELECT … FROM … JOIN … WHERE … GROUP BY … HAVING … ORDER BY. When you meet a long query, find the clause keywords first. They tell you where the columns are defined, where the data comes from, how it is filtered, and how it is summarized and sorted.
Formatting conventions
SQL Server does not care about layout, but the people who read your queries do. The conventions used throughout this series are common in SQL Server teams:
- Keywords in uppercase:
SELECT,FROM,WHERE,GROUP BY. They stand out from table and column names. - One clause per line, starting at the left margin, so the structure is visible at a glance.
- One column per line in long
SELECTlists, indented underSELECT. Adding, removing or commenting out a column is then a one-line change. - Two-part names for tables:
dbo.Customers, notCustomers. - Aliases with
AS:COUNT(*) AS CustomerCountreads better thanCOUNT(*) CustomerCount, and it avoids the missing-comma surprise from lesson 3. - A semicolon at the end of each statement.
Some teams put commas at the start of the line instead of the end, or indent AND and OR conditions under WHERE. Any consistent style is fine. What matters is that each clause is easy to find.
Common mistakes with clauses
Clauses in the wrong order. SELECT State FROM dbo.Customers ORDER BY State WHERE State <> 'FL'; fails with “Incorrect syntax near the keyword ‘WHERE’.” WHERE must come before ORDER BY.
Filtering an aggregate in WHERE. WHERE COUNT(*) > 1 is not allowed: WHERE works on individual rows, before groups exist. Conditions on groups go in HAVING. The next lesson explains why.
Selecting a column that is not grouped. With GROUP BY State, writing SELECT State, City, COUNT(*) fails, because a group of three Florida customers has three different cities and SQL Server cannot pick one. Every column in the SELECT list must either be in GROUP BY or be inside an aggregate function.
Expecting an order without ORDER BY. A result that happens to come back sorted is not guaranteed to stay sorted.
Next step
You can now name every clause in a SELECT statement and say what it does. The next lesson, SQL Query Order: Written Order vs Logical Processing Order, explains the order in which those clauses are logically evaluated, and why that order explains the errors above.
The complete path is on the SQL Server Foundations hub.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 4 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetCheck SQL Server Table SizesMeasure SQL Server table row counts and storage usage, separate data from index space, and identify which tables are driving database growth.Open cheat sheet
- RoadmapSQL Server FoundationsWhat each clause of a T-SQL SELECT statement does, which parts are required and which are optional, and how one complete query reads from SELECT to ORDER BY.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.