The SELECT statement is the workhorse of SQL. Almost everything you do with a database, from a quick lookup to a dashboard query, is a SELECT. This guide starts with the anatomy of the statement, shows a tiny sample schema, then walks 25 numbered examples that build from a single column all the way to a window function.
What this guide covers
SELECT anatomy and clause order
A SELECT statement is built from a fixed set of clauses that must appear in one specific written order. You may leave clauses out, but you may never reorder them. Here is the skeleton with a one line note on each part:
SELECT -- 1. which columns or expressions to return
FROM -- 2. source tables and joins
WHERE -- 3. keep only rows that match a condition
GROUP BY -- 4. collapse rows into groups
HAVING -- 5. filter the groups
ORDER BY -- 6. sort the result
LIMIT -- 7. cap the number of rows returnedThere is one twist that trips up almost everyone. The written order above is not the order the database evaluates the clauses in. The engine reads FROM first, then WHERE, then GROUP BY, then HAVING, then the SELECT list, then ORDER BY, and LIMIT last. That is why a column alias you create in SELECT cannot be used back in WHERE: at the moment WHERE runs, the alias does not exist yet. Hold that logical order in your head and most surprising results explain themselves.
The clauses have a friendly job description each. The table below is worth keeping open as you read the examples, because every query that follows is just a subset of these seven clauses in this exact sequence.
| Clause | What it does | Required? |
|---|---|---|
| SELECT | Chooses the columns and expressions in the output | Yes |
| FROM | Names the tables and how they join | Usually |
| WHERE | Filters individual rows before grouping | No |
| GROUP BY | Folds rows into groups for aggregation | No |
| HAVING | Filters groups after aggregation | No |
| ORDER BY | Sorts the final result set | No |
| LIMIT | Returns at most N rows | No |
Those seven clauses do not appear with equal frequency in real code. SELECT and FROM are in almost every query, WHERE in most, and the grouping and window machinery in a smaller slice. The chart below is a rough guide to how often each clause turns up in everyday application queries.
The sample schema
Every example uses the same two small tables: customers and their orders. Here is the shape of the data and a few sample rows so you can predict what each query returns.
-- customers: one row per customer
customer_id, name, city, country, created_at
-- orders: many rows per customer, linked by customer_id
order_id, customer_id, amount, status, order_date| customer_id | name | city | country |
|---|---|---|---|
| 1 | Ada Lovelace | London | UK |
| 2 | Grace Hopper | New York | USA |
| 3 | Linus Torvalds | Portland | USA |
| 4 | Guido van Rossum | Amsterdam | NL |
For a broader set of runnable snippets, keep the SQL examples library and the SQL cheat sheet open alongside this page. SELECT itself belongs to the query family of SQL, covered in the data query language commands.
Examples 1 to 6: columns, DISTINCT, aliases
1. Return every column with a star. The quickest way to see a table is to select all of its columns.
SELECT * FROM customers;2. Pick only the columns you need. Naming columns explicitly keeps output narrow and stable when the table changes.
SELECT name, city FROM customers;Habit worth forming: avoid SELECT * in application code. Listing columns is clearer, moves less data, and does not break when someone adds a column later.
3. Rename output with a column alias. The AS keyword gives a column a friendlier heading in the result.
SELECT name AS customer, city AS location
FROM customers;4. Compute a value in the SELECT list. Any expression can be a column, including arithmetic on other columns.
SELECT order_id,
amount,
amount * 1.2 AS amount_with_tax
FROM orders;5. Remove duplicate rows with DISTINCT. Here we ask for the distinct list of countries our customers live in. DISTINCT applies to the whole row you project, not just the first column, so selecting two columns returns each unique combination of the pair.
SELECT DISTINCT country FROM customers;6. Join text together. Concatenation builds a display string from several columns. The portable choice is CONCAT().
SELECT CONCAT(name, ' (', city, ')') AS label
FROM customers;
-- PostgreSQL and the SQL standard also allow: name || ' (' || city || ')'Examples 7 to 12: filtering with WHERE
The WHERE clause keeps only the rows that satisfy a condition. It runs before grouping and before the SELECT list is computed. For a deeper tour of predicates, see the SQL WHERE clause explained guide.
7. Match an exact value. Return only the customers based in the USA.
SELECT name, city FROM customers
WHERE country = 'USA';8. Combine conditions with AND and OR. Parentheses make the logic explicit so the two ideas do not blur together.
SELECT * FROM orders
WHERE status = 'paid'
AND (amount > 100 OR customer_id = 1);9. Test a range with BETWEEN. BETWEEN is inclusive on both ends, so this covers 50 and 200 as well.
SELECT order_id, amount FROM orders
WHERE amount BETWEEN 50 AND 200;10. Match any value in a set with IN. IN is a clean shorthand for several OR comparisons.
SELECT name, country FROM customers
WHERE country IN ('UK', 'NL');11. Pattern match with LIKE. The percent sign is a wildcard for any run of characters, and the underscore matches exactly one character. This finds every name that starts with a capital G. Leading wildcards such as '%son' are powerful but slow, because the engine cannot use a normal index to jump straight to the match.
SELECT name FROM customers
WHERE name LIKE 'G%';12. Find missing values with IS NULL. You cannot compare to NULL with an equals sign, because any comparison with NULL is unknown. Use IS NULL instead.
SELECT order_id FROM orders
WHERE status IS NULL;Examples 13 to 15: ORDER BY and LIMIT
13. Sort the result. ORDER BY defaults to ascending order. Sort customers alphabetically by name.
SELECT name, city FROM customers
ORDER BY name;14. Sort on more than one key. Sort by country first, then by the largest orders inside each country using DESC.
SELECT c.country, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY c.country ASC, o.amount DESC;15. Cap the rows and page through them. LIMIT returns at most N rows; OFFSET skips rows so you can paginate. This grabs the second page of five.
SELECT order_id, amount FROM orders
ORDER BY amount DESC
LIMIT 5 OFFSET 5;
-- SQL Server spells this OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLYExamples 16 to 19: GROUP BY and HAVING
Aggregation collapses many rows into one summary row. GROUP BY decides how rows are bucketed, and HAVING filters those buckets. The order and reasoning behind these clauses are laid out in the ORDER BY, GROUP BY and HAVING guide.
16. Count rows. COUNT is the simplest aggregate. How many orders are there in total?
SELECT COUNT(*) AS total_orders FROM orders;17. Summarise numbers. SUM, AVG, MIN and MAX turn a column into a single figure each.
SELECT SUM(amount) AS revenue,
AVG(amount) AS avg_order,
MIN(amount) AS smallest,
MAX(amount) AS largest
FROM orders;18. Aggregate per group. GROUP BY produces one summary row per customer. Every non aggregated column in the SELECT list must appear in GROUP BY.
SELECT customer_id,
COUNT(*) AS orders_count,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;A grouped query returns one row per bucket. For our orders, the result looks like this:
| customer_id | orders_count | total_spent |
|---|---|---|
| 1 | 3 | 420.00 |
| 2 | 5 | 1180.00 |
| 3 | 1 | 65.00 |
| 4 | 2 | 240.00 |
19. Filter groups with HAVING. WHERE cannot see aggregates because it runs before grouping. HAVING runs after, so it can. Keep only customers who spent more than 300.
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 300;Watch out: use WHERE to filter rows and HAVING to filter groups. Putting a plain row condition in HAVING still works but scans more rows than it needs to. Filter early with WHERE, then aggregate.
Examples 20 to 22: joining tables
Joins let one query read from more than one table. The full mental model, with diagrams, lives in the SQL joins guide.
The ON clause states how the tables line up, almost always a foreign key matching a primary key. Give each table a short alias so column references stay readable, and qualify shared column names so the engine never has to guess which table you mean.
20. INNER JOIN keeps matches only. Pair each order with the customer who placed it. Rows with no match on either side drop out.
SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;21. LEFT JOIN keeps every left row. This lists all customers and their orders, including customers who have never ordered. Their order columns come back as NULL.
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;22. Join then aggregate. Combine a join with GROUP BY to report revenue by country. This is the everyday shape of a reporting query.
SELECT c.country,
COUNT(o.order_id) AS orders_count,
SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.country
ORDER BY revenue DESC;Examples 23 to 25: subquery, CASE, window
The last three examples show SELECT doing real analytical work. Each one keeps the same clause order you have already learned and simply adds an expression or a nested query on top.
23. Filter with a subquery. A subquery is a SELECT nested inside another statement. Here it produces the set of customer ids that have at least one paid order, and the outer query uses that set. When the inner list can contain NULLs, prefer EXISTS or NOT EXISTS over IN and NOT IN, which behave unexpectedly around unknown values.
SELECT name, city FROM customers
WHERE customer_id IN (
SELECT customer_id FROM orders
WHERE status = 'paid'
);24. Branch with CASE. CASE is an inline conditional that returns a value. Use it to bucket each order into a size label right in the SELECT list.
SELECT order_id, amount,
CASE
WHEN amount >= 200 THEN 'large'
WHEN amount >= 50 THEN 'medium'
ELSE 'small'
END AS size_band
FROM orders;25. Rank without collapsing rows with a window function. A window function computes across a set of rows but keeps every row in the output. Here ROW_NUMBER() numbers each customer's orders from largest to smallest.
SELECT customer_id, order_id, amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC
) AS rank_in_customer
FROM orders;Because the window keeps all rows, the output has one line per order plus a running rank inside each customer:
| customer_id | order_id | amount | rank_in_customer |
|---|---|---|---|
| 2 | 18 | 500.00 | 1 |
| 2 | 14 | 300.00 | 2 |
| 2 | 21 | 180.00 | 3 |
| 1 | 7 | 220.00 | 1 |
Window functions are where SELECT stops feeling like a filter and starts feeling like a small analytics engine. To go deeper, the official references are excellent: the MySQL SELECT syntax reference and the PostgreSQL SELECT documentation both lay out every clause in precise detail.
Where to go next
You now have a query for every common shape: columns and aliases, filtering, sorting and paging, aggregation, joins, subqueries, CASE, and a window function. The fastest way to make these stick is to type them against a real table and change one thing at a time. When you are ready for structured practice, the SQL beginner course walks these clauses in the same order with exercises after each one.