SELECT Query Structure
A SELECT statement is built from clauses that always appear in the same order; this chapter uses SELECT, FROM, WHERE and ORDER BY.
Novice · T-SQL · SQL · Updated
After this lesson you can
- Put the clauses of a SELECT statement in the right order
Key takeaways
- A
SELECTis built from clauses that must appear in a fixed order: in this chapter,SELECT,FROM,WHERE, thenORDER BY. - Every clause is optional except
SELECTitself. - Keywords aren't case-sensitive and line breaks don't matter, but capital keywords and one clause per line make a query easier to read.
- A semicolon
;ends a statement, and writing it is a good habit. - T-SQL limits rows with
TOP, while MySQL and PostgreSQL useLIMIT, so check which database an answer on the web is written for.
A SELECT statement is built from clauses: a keyword followed by what it applies to. The SQL standard fixes which clauses there are and the order they must appear in. You'll learn them a few at a time; here's the whole shape, so you can see where each one fits:
SELECT TOP (<how many rows>) <columns>
FROM <table>
JOIN <another table> ON <matching columns>
WHERE <which rows>
GROUP BY <columns to group by>
HAVING <which groups>
ORDER BY <columns to sort by>
Every clause is optional except SELECT itself, but the ones you use must appear in this order. In this chapter you'll use four of them:
| Clause | What it does |
|---|---|
SELECT |
Chooses the columns, or calculated values, to return. TOP limits how many rows. |
FROM |
Names the table the rows come from |
WHERE |
Keeps only the rows that match a condition |
ORDER BY |
Sorts the result |
JOIN, GROUP BY and HAVING come in Module 2.
A few rules of style
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > 50
ORDER BY UnitPrice DESC;
- Keywords aren't case-sensitive.
selectworks as well asSELECT, but writing keywords in capitals makes queries easier to read, and it's the common style. - Line breaks don't matter to SQL. Putting each clause on its own line is for people.
- A semicolon ends a statement. In a window with one query you can leave it off, but it's a good habit.
Other databases, other words
Most SQL works in any database, but each has a few words of its own. T-SQL limits rows with TOP; MySQL and PostgreSQL use LIMIT at the end of the query instead. If you search the web for SQL help, check which database an answer is written for.
Do it: put the clauses in order
A teammate wrote a query for the price list: the products that cost more than $20, most expensive first. The four clauses are all right, but they're in the wrong order, so the database refuses to run it.
The Products table (the Tables tab shows the SQL that makes it) has these rows:
| ProductName | UnitPrice |
|---|---|
| Chai | 18 |
| Chang | 19 |
| Aniseed Syrup | 10 |
| Chef Anton's Cajun Seasoning | 22 |
| Grandma's Boysenberry Spread | 25 |
| Uncle Bob's Organic Dried Pears | 30 |
| Northwoods Cranberry Sauce | 40 |
| Mishi Kobe Niku | 97 |
Once the clauses are in order, the query gives exactly these rows, in this order:
| ProductName | UnitPrice |
|---|---|
| Mishi Kobe Niku | 97 |
| Northwoods Cranberry Sauce | 40 |
| Uncle Bob's Organic Dried Pears | 30 |
| Grandma's Boysenberry Spread | 25 |
| Chef Anton's Cajun Seasoning | 22 |
Your job: put the query's four clauses in the order SQL needs, so it runs.
- Keep each clause as it is:
SELECT ProductName, UnitPrice,FROM Products,WHERE UnitPrice > 20andORDER BY UnitPrice DESC. - Only their order changes.
Practiced live on the lesson page: write the SQL right there and check it.
Check yourself
1 question to try on the lesson page.
So what
Knowing the order of the clauses lets you read any query at a glance, and write your own without guessing where each part goes.