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.

By JaviPublished 9 min readBeginner

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:

SQL query: A SELECT statement is a list of clausesT-SQL
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)
T-SQL7 linesRead-only query display. It is not connected to a database.

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.

    1. RequiredSELECT
    2. Columns and output
    1. Usual startFROM
    2. Source table
    1. OptionalJOIN
    2. Combine tables
    1. OptionalWHERE
    2. Filter rows
    1. OptionalGROUP BY
    2. Create groups
    1. OptionalHAVING
    2. Filter groups
    1. OptionalORDER BY
    2. Sort final result
The anatomy of a SELECT statement. Each clause has one job. SELECT and FROM are where every query starts; the others are added when the question needs them.

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:

SQL query: The required parts: SELECT and FROMT-SQL
SELECT
    CustomerId,
    FirstName + ' ' + LastName AS FullName
FROM dbo.Customers;
T-SQL4 linesRead-only query display. It is not connected to a database.

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?

SQL query: One complete queryT-SQL
SELECT
    State,
    COUNT(*) AS CustomerCount
FROM dbo.Customers
WHERE State IS NOT NULL
GROUP BY State
HAVING COUNT(*) > 1
ORDER BY CustomerCount DESC;
T-SQL8 linesRead-only query display. It is not connected to a database.

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 CustomerCount asks for two output columns: the state code, and the number of rows in each group, named CustomerCount.
  • FROM dbo.Customers reads the practice table.
  • WHERE State IS NOT NULL drops 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 NULL is the correct way to test for a missing value; = NULL does not work, as the WHERE lesson explains.)
  • GROUP BY State builds one group per state: three customers in FL, two in TX, one each in WA, CO, OR, MA and IL.
  • HAVING COUNT(*) > 1 keeps only groups with more than one customer: FL and TX.
  • ORDER BY CustomerCount DESC sorts the result from the largest count to the smallest. DESC means 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:

SQL query: Simple queries and advanced queries use the same clausesT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName
FROM dbo.Customers
WHERE State = 'FL';
T-SQL6 linesRead-only query display. It is not connected to a database.

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 SELECT lists, indented under SELECT. Adding, removing or commenting out a column is then a one-line change.
  • Two-part names for tables: dbo.Customers, not Customers.
  • Aliases with AS: COUNT(*) AS CustomerCount reads better than COUNT(*) 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

Question 1 of 2What does the SELECT list specify?
Question 2 of 2What is the role of FROM in a basic SELECT?

SQL Server Foundations

Lesson 4 of 25

Continue to next lesson

SQL Query Order: Written Order vs Logical Processing Order

Continue to Lesson 5

Tags

  • SQL Server
  • SQL Fundamentals
  • SSMS
  • T-SQL