Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT name, price
FROM products
WHERE price < 20
ORDER BY price;

Try it

SQL Choosing columns and computing values ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL SELECT without a table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
-- 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 BY in every dialect, but not in WHERE (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 / 4 is 2 in SQLite, PostgreSQL and SQL Server, but 2.5 in MySQL and Oracle. Use a decimal literal or CAST to 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.