SELECT is the SQL statement that retrieves data from one or more tables, views or derived results and returns it as a result set of rows and columns. It is declarative: the query states which data is wanted, under which conditions and in which order, while the database engine’s query optimizer decides how to read indexes, join tables and sort the output.
In SQL and relational databases, SELECT is by far the most frequently executed statement and the foundation for nearly every other query feature, including JOIN, GROUP BY, HAVING, subqueries, CTEs and window functions. A plain SELECT does not modify data, but its correctness and performance determine whether reports are right and whether an application stays fast as tables grow.
Structure of a SELECT statement
A SELECT statement consists of a mandatory select list and a series of optional clauses, each of which narrows, combines or shapes the data. The basic form is SELECT columns FROM table WHERE condition ORDER BY column, and every clause has a precisely defined role.
The table lists the clauses in the order in which they are written and explains what each one does.
| Clause | Purpose | Example |
|---|---|---|
| SELECT | Lists the columns or expressions to return | SELECT name, price * 1.2 AS gross |
| FROM | Names the source tables, views or subqueries | FROM products p |
| JOIN … ON | Combines rows from related tables | JOIN categories c ON c.id = p.category_id |
| WHERE | Filters individual rows before grouping | WHERE p.active = TRUE |
| GROUP BY | Collapses rows into groups for aggregation | GROUP BY c.name |
| HAVING | Filters groups after aggregation | HAVING COUNT(*) > 10 |
| ORDER BY | Sorts the final result | ORDER BY gross DESC |
| LIMIT or FETCH FIRST | Restricts the number of returned rows | FETCH FIRST 20 ROWS ONLY |
The keyword DISTINCT after SELECT removes duplicate rows from the result, and column aliases created with AS rename output columns. Without ORDER BY, the order of rows is not guaranteed, even if a query happens to return them sorted during testing.
Logical order of evaluation
A SELECT statement is not evaluated in the order it is written. Logically, the engine first builds the source rows, then filters, groups and projects them, and only at the end sorts and limits the result. This logical order explains many error messages and surprising results.
The following table shows the logical processing order and its practical consequence.
| Step | Clause | Practical consequence |
|---|---|---|
| 1 | FROM and JOIN | All source rows and join results are formed first |
| 2 | WHERE | Cannot use aggregates or most select-list aliases |
| 3 | GROUP BY | Each output row now represents a group |
| 4 | HAVING | Can filter on aggregates such as SUM or COUNT |
| 5 | Window functions | Computed over the grouped and filtered rows |
| 6 | SELECT list | Expressions and aliases are evaluated here |
| 7 | DISTINCT | Duplicates are removed from the projected rows |
| 8 | ORDER BY | Can usually refer to select-list aliases |
| 9 | LIMIT, OFFSET or FETCH | Applied last, to the sorted result |
This is a logical model, not the physical execution plan. The optimizer may push filters into joins, use an index to avoid sorting or stop reading early once the limit is reached, as long as the result is the same as the logical order would produce.
Dialect differences across database systems
The core of SELECT is defined by the ISO/IEC 9075 SQL standard, but database systems differ in details, especially in how they limit rows and handle grouping. Portable code relies on the standard forms where the target systems support them.
The table compares common syntax for returning the first ten rows and related behavior.
| System | First ten rows | Notable behavior |
|---|---|---|
| PostgreSQL | LIMIT 10 or FETCH FIRST 10 ROWS ONLY | Strict GROUP BY rules; supports DISTINCT ON |
| MySQL | LIMIT 10 | ONLY_FULL_GROUP_BY enforced by default since 5.7 |
| SQL Server | SELECT TOP 10 or OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY | OFFSET and FETCH require ORDER BY |
| Oracle Database | FETCH FIRST 10 ROWS ONLY since 12c | Older code uses ROWNUM, applied before ORDER BY |
| SQLite | LIMIT 10 | Dynamic typing can affect comparisons |
The Oracle ROWNUM case illustrates why the logical order matters: filtering on ROWNUM in the same query level as ORDER BY returns ten arbitrary rows, sorted afterward, rather than the top ten. The standard FETCH FIRST syntax avoids this trap.
Writing efficient SELECT queries
The performance of a SELECT depends mainly on how many rows the engine must read, not on how many it returns. A query that filters on an indexed column with good selectivity can locate a few rows among millions in milliseconds, while the same query without a usable index scans the whole table.
- Select only the needed columns instead of
SELECT *; this reduces input and output, network transfer and enables covering indexes. - Keep WHERE conditions sargable: wrapping an indexed column in a function, as in
WHERE YEAR(created_at) = 2025, usually prevents index use, while a range condition on the raw column does not. - Use keyset pagination (
WHERE id > last_seen ORDER BY id LIMIT 50) for deep pages, because a large OFFSET still reads and discards every skipped row. - Inspect the execution plan with EXPLAIN or the system’s equivalent before tuning, rather than guessing.
- Use parameterized queries so that values never become part of the SQL text, which prevents SQL injection and allows plan reuse.
Common mistakes with SELECT
Most SELECT errors produce results that look plausible but are wrong, which makes them more dangerous than syntax errors. The table lists frequent mistakes, their symptoms and the correction.
| Mistake | Symptom | Correction |
|---|---|---|
| Comparing with = NULL | No rows returned | Use IS NULL or IS NOT NULL |
| NOT IN with a NULL in the list | Empty result despite matching data | Use NOT EXISTS instead |
| Missing join condition | Row count explodes to a Cartesian product | Join on the correct key columns |
| COUNT(column) instead of COUNT(*) | Counts skip rows where the column is NULL | Choose the count deliberately |
| Relying on implicit order | Row order changes after an upgrade or new index | Always specify ORDER BY |
| Filtering aggregates in WHERE | Syntax error | Move the condition to HAVING |
These mistakes share one root cause: SQL uses three-valued logic with TRUE, FALSE and UNKNOWN, and any comparison with NULL yields UNKNOWN, which WHERE treats as not satisfied.
Put simply
SELECT is how you ask a database a question. You describe what you want to see, and the database finds it for you. It is like asking a librarian for all mystery novels published after 2010, sorted by author. You do not tell the librarian which shelves to walk past.
The answer always comes back as a neat table of rows and columns. Asking the question never changes the books themselves. The main skill is phrasing the question precisely, because a slightly wrong question still gets an answer, just not the right one.
In practice
An analyst at an online retailer needs the five product categories with the highest revenue in the last full quarter, counting only completed orders. The data lives in three tables: orders, order_items and products.
The query joins the tables, filters with WHERE o.status = 'completed' AND o.created_at >= DATE '2026-07-01' AND o.created_at < DATE '2026-10-01', groups by category, sums quantity times unit price, sorts by that sum in descending order and fetches the first five rows. Using a half-open date range instead of wrapping the date column in a function lets the engine use the index on created_at, and it also includes orders placed late on September 30.
On a table of about 12 million orders, the execution plan shows an index range scan over roughly one quarter’s rows instead of a full scan, and the query returns in under a second. The analyst saves it as a view so the monthly report runs the same verified logic every time.