SQL guide · Querying

SQL SELECT: Retrieve Data From a Table

The SELECT statement is the command you use to read data from a database. It returns a result set, which is a temporary table made of the columns and rows you asked for. Nearly every report, dashboard and analysis starts with a SELECT, and it is the first statement most people learn because it never changes the stored data.

A query lists the columns or expressions you want after the SELECT keyword, then names the source in a FROM clause. You can add WHERE to keep only matching rows, GROUP BY to summarise, HAVING to filter groups, and ORDER BY to sort. The database does not run these clauses in the order you write them. Logically it evaluates FROM first, then WHERE, GROUP BY, HAVING, SELECT and finally ORDER BY, which explains why a column alias defined in SELECT cannot be used in WHERE in most databases.

Use SELECT * for quick exploration, but list the columns explicitly in application code, views and scheduled reports. Naming columns keeps the result stable when someone adds a column to the table, reduces the amount of data sent over the network, and lets the optimizer use covering indexes. A SELECT can also work without a table at all, for example SELECT 1 + 1 or SELECT CURRENT_DATE, which is handy for testing an expression.

Basic syntax

SELECT column1, column2, expression AS alias
FROM table_name
WHERE condition
GROUP BY column1
HAVING group_condition
ORDER BY column1 [ASC | DESC];

Only SELECT is required in most databases; every other clause is optional but must appear in the order shown. Use * to return every column, or a comma-separated list of columns and expressions. An expression can be arithmetic, a function call, a CASE expression or a subquery that returns one value.

SQL SELECT examples

Select specific columns

List only the columns you need. The result contains one row per row in the table, with the columns in the order you wrote them, not the order they were defined in the table.

SELECT first_name, last_name, email
FROM customers;

Select all columns

The asterisk expands to every column in the table. This is convenient while exploring an unfamiliar table, and combined with a row limit it gives you a quick preview of the data.

SELECT *
FROM products;

Compute values with expressions

You can calculate new columns directly in the select list. Here each order line gets a total, and an alias gives the calculated column a readable name in the output.

SELECT order_id,
       quantity,
       unit_price,
       quantity * unit_price AS line_total
FROM order_items;

Filter and sort the result

Combine SELECT with WHERE and ORDER BY to answer a concrete question. This returns the ten most recent orders over 100 dollars, newest first. The row-limiting syntax differs by database; see the dialect notes.

SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE total_amount > 100
ORDER BY order_date DESC
LIMIT 10;

Where SQL dialects differ

PostgreSQL: Allows SELECT without FROM, supports LIMIT and OFFSET, and also the standard FETCH FIRST n ROWS ONLY. DISTINCT ON (col) is a PostgreSQL-only extension for keeping the first row per group. Unquoted identifiers are folded to lower case.

MySQL: Uses LIMIT n to restrict rows and accepts SELECT without FROM. Backticks quote identifiers, and table names are case sensitive on Linux but not on Windows by default. Window functions and common table expressions need MySQL 8.0 or later.

SQL Server: Uses SELECT TOP (n) instead of LIMIT, or OFFSET ... FETCH NEXT after ORDER BY on SQL Server 2012 and later. Square brackets quote identifiers. A bare SELECT without FROM is allowed.

SQLite: Supports LIMIT and OFFSET, and SELECT without FROM. SQLite is loosely typed, so a column can hold values that do not match its declared type, which occasionally surprises people when sorting or comparing.

Oracle: Requires a FROM clause, so use SELECT 1 FROM dual for table-less expressions. Row limiting uses FETCH FIRST n ROWS ONLY in Oracle 12c and later, or ROWNUM in older versions.

Common mistakes

  • Using SELECT * in production code. When a column is added, renamed or removed, the query output changes silently and can break the code that reads it.
  • Referring to a column alias in the WHERE clause. The alias is created after WHERE is evaluated, so repeat the expression or wrap the query in a subquery or common table expression.
  • Forgetting ORDER BY and assuming rows come back in insertion order. Without an explicit sort, the row order is not guaranteed and can change between runs.
  • Selecting a non-aggregated column alongside an aggregate without listing it in GROUP BY. Most databases raise an error, and MySQL with relaxed settings returns an arbitrary value.
  • Running an unfiltered SELECT on a very large table. Add a WHERE clause or a row limit while developing so you do not scan millions of rows by accident.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator