SQL Reference ยท Statements and Clauses
SQL SELECT statement
Reference for the SQL SELECT statement: choose columns and expressions, add clauses, SELECT without FROM, SELECT *, and dialect differences.
SELECT is the statement that reads data. It takes rows from one or more tables, keeps the ones you ask for, computes the columns you list, and returns the result as a new table. Every report, search page and dashboard starts with a SELECT.
A query is built from optional clauses that the database evaluates in a fixed logical order: FROM, WHERE, GROUP BY, HAVING, the select list itself, ORDER BY, and finally LIMIT. You write them in a slightly different order, but each clause only sees what the earlier ones produced.
SELECT name, price FROM products WHERE price < 20 ORDER BY price;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT [ DISTINCT | ALL ] select_item [, select_item ...] [ FROM table_expression ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition ] [ ORDER BY expression [ ASC | DESC ] [, ...] ] [ LIMIT count [ OFFSET skip ] ];
| Part | Description |
|---|---|
select_item |
A column, an expression, a function call, a literal, or * for every column. Add AS name to give the output column a name. |
DISTINCT / ALL |
DISTINCT removes duplicate rows from the result; ALL (the default) keeps them. See SELECT DISTINCT. |
FROM |
The tables, views or subqueries to read. Optional in most dialects. |
WHERE |
Keeps only rows for which the condition is true. |
GROUP BY / HAVING |
Collapses rows into groups, then filters the groups. |
ORDER BY |
Sorts the result. Without it, row order is not guaranteed. |
LIMIT / OFFSET |
Caps the number of rows and skips some from the start. |
Returns: a result set with one column per select item and zero or more rows.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
SELECT without FROM |
Yes | Yes | Yes | Yes | No: use FROM dual (23ai allows it) |
| Limit rows | LIMIT |
LIMIT or FETCH FIRST |
LIMIT |
TOP or FETCH |
FETCH FIRST (12c+) |
| Quote identifiers | "x" or [x] |
"x" |
`x` |
[x] or "x" |
"x" |
-- SQL Server SELECT TOP 5 name, price FROM products ORDER BY price DESC; -- Oracle (before 23ai) SELECT 6 * 7 AS answer FROM dual;
Usage notes
- Row order is not guaranteed unless you write
ORDER BY. The same query may return rows in different orders after an index or a data change. - Avoid
SELECT *in application code. It returns columns you do not need, breaks when a table gains columns, and prevents covering-index plans. It is fine for exploring. - Aliases from the select list can be used in
ORDER BYin every dialect, but not inWHERE(the select list is evaluated later). SQLite and MySQL are lenient in some other clauses; portable code does not rely on that. - Integer division:
10 / 4is2in SQLite, PostgreSQL and SQL Server, but2.5in MySQL and Oracle. Use a decimal literal orCASTto be explicit. - NULL in an expression usually makes the whole expression NULL; see NULL and three-valued logic.
- Column names in the result come from the column or the alias. Unaliased expressions get a dialect-specific name, so always alias expressions you will refer to.
Related
SELECT DISTINCT,FROM,WHERE,ORDER BY,AS- Lessons: SELECT in depth, Querying with WHERE, SQL dialects