Querying
The SELECT Statement
The SELECT statement is the primary means of retrieving data from a relational database.
Overview
The SELECT statement is the primary means of retrieving data from a relational database. It reads rows from one or more tables or derived relations and returns a result set to the client. Although SELECT is often described as a query language feature, it is formally part of the Data Manipulation Language (DML) defined in SQL-86, the first published SQL standard. A SELECT statement does not modify stored data; it produces a virtual table whose columns and rows are defined by the query's select list, FROM clause, and optional clauses. The result set may be consumed directly by an application, embedded in a larger expression, or stored through mechanisms such as CREATE TABLE AS SELECT or INSERT ... SELECT. Because SELECT is declarative, the user specifies what data is wanted rather than how to obtain it. The database optimizer chooses an execution plan, which may involve index scans, joins, sorting, or aggregation. This separation of specification from execution is central to SQL's portability and to the role of the query planner. SELECT is also the foundation for views, subqueries, common table expressions, and set operations, making it the most frequently used statement in analytical and transactional workloads alike.
Syntax and usage
The core form is SELECT column_list FROM table_reference [WHERE condition] [GROUP BY grouping_columns] [HAVING group_condition] [ORDER BY sort_columns]. The select list names the columns or expressions to return; * denotes all columns of the source relations. The FROM clause identifies one or more tables, views, or derived tables, and may include join specifications. The WHERE clause filters rows before grouping. GROUP BY collapses rows into groups, and HAVING filters those groups. ORDER BY sorts the final result and is the only clause that guarantees row order. DISTINCT removes duplicate rows from the result. Standard SQL also defines set operators such as UNION, INTERSECT, and EXCEPT, which combine the results of two SELECT statements with compatible column counts and types. Vendor extensions are common: many systems support LIMIT or FETCH FIRST for row limiting, TOP in some dialects, and window functions introduced in SQL:2003. Parameter placeholders, such as ? or :name, allow prepared statements. SELECT may appear as a subquery in expressions, in the FROM clause as a derived table, or in a WITH clause as a named common table expression. These forms are logically equivalent to nested queries but often improve readability.
How it works
Conceptually, a SELECT statement is evaluated in a defined logical order that differs from its written order. The FROM clause is processed first, producing a working set of rows, including the effect of joins. The WHERE clause then filters that set. If GROUP BY is present, remaining rows are grouped by the specified expressions, and aggregate functions such as COUNT, SUM, AVG, MIN, and MAX are computed per group. HAVING then removes groups that fail its condition. The select list is evaluated next, computing expressions and applying DISTINCT if requested. Finally, ORDER BY sorts the result, and any row-limiting clause is applied. This logical sequence explains why column aliases defined in the select list are generally not visible in WHERE, while they may be visible in ORDER BY in many implementations. Physically, the optimizer translates the query into an execution plan of operators: scans, filters, joins, aggregations, sorts, and projections. Join order, access methods, and parallelism are chosen based on statistics and cost estimates. Because SQL is declarative, two queries with identical logical meaning can have very different performance. Understanding the logical order helps developers predict which predicates can use indexes and where aggregation or sorting costs arise.
Examples
A basic projection and filter returns selected columns from one table: SELECT customer_id, order_date, total FROM orders WHERE total > 100 ORDER BY order_date DESC. This query reads the orders table, keeps rows whose total exceeds 100, and returns them newest first. An aggregate query groups rows and applies a condition to the groups: SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue FROM orders GROUP BY customer_id HAVING COUNT(*) >= 3. Here COUNT and SUM are computed per customer, and HAVING keeps only customers with at least three orders. A join combines related tables: SELECT c.name, o.order_date FROM customers AS c JOIN orders AS o ON c.customer_id = o.customer_id WHERE o.order_date >= DATE '2024-01-01'. The ON clause states the join predicate, while WHERE filters the joined rows. A subquery can supply a value or a set: SELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE total > 500). Set operations combine results: SELECT email FROM customers UNION SELECT email FROM prospects. Each example returns a result set and leaves stored data unchanged.
Common pitfalls
A frequent mistake is assuming that a query without ORDER BY returns rows in a particular order. Relational tables are unordered; only ORDER BY defines result order. Another pitfall is using SELECT * in production code, which couples applications to column order and can break when schemas change. Aggregation errors arise when non-aggregated columns appear in the select list without being named in GROUP BY; standard SQL rejects this, though some systems permit it with indeterminate results. Confusing WHERE and HAVING is common: WHERE filters rows before grouping, HAVING filters groups after aggregation. NULL handling also surprises many users, because comparisons with NULL yield UNKNOWN rather than TRUE, and COUNT(*) counts rows while COUNT(column) ignores NULLs. Duplicate rows may appear when joins multiply matches; DISTINCT can hide the problem but often masks a missing join condition or an incorrect grain. Implicit joins written with comma-separated tables and a WHERE predicate are legal but error-prone and harder to read than explicit JOIN syntax. Finally, row-limiting clauses are not fully portable: LIMIT, TOP, and FETCH FIRST differ across vendors, so portable code should isolate such constructs.
Best practices
Name columns explicitly instead of relying on SELECT *, especially in views, application queries, and INSERT ... SELECT statements. Qualify column references with table aliases when more than one table is involved, which prevents ambiguity and improves readability. Use explicit JOIN syntax with an ON clause rather than comma joins, and keep join predicates close to the tables they connect. Push filtering into WHERE so the optimizer can use indexes and reduce intermediate row counts. Use GROUP BY only when aggregation is intended, and place group-level conditions in HAVING. Add ORDER BY whenever result order matters to the consumer, and pair it with a deterministic sort key. For large results, apply row limiting deliberately and consider pagination strategies based on stable keys rather than offsets. Format SQL consistently, with keywords in a consistent case and clauses on separate lines. Test queries against realistic data volumes, and inspect execution plans when performance is critical. Avoid functions on indexed columns in predicates when a sargable rewrite is possible. Document assumptions about data grain, uniqueness, and NULLability, since these determine whether a query returns the expected rows.
Frequently asked questions
What is the difference between WHERE and HAVING? WHERE filters individual rows before grouping and cannot reference aggregate functions. HAVING filters groups after GROUP BY and aggregation, so it can contain aggregates such as HAVING COUNT(*) > 1. A query may use both. Does SELECT modify data? No. SELECT only reads and returns a result set. Data changes require INSERT, UPDATE, DELETE, or MERGE. However, SELECT can participate in data modification through statements like INSERT INTO archive SELECT * FROM orders WHERE order_date < DATE '2020-01-01', where the SELECT supplies rows to be inserted. Why does my query return duplicate rows? Duplicates usually come from a join that matches multiple rows on one side, from a missing or incorrect join predicate, or from querying a table whose grain is finer than expected. Inspecting the join keys and counting rows before and after each join step usually identifies the cause. DISTINCT can remove duplicates, but it is better to correct the join or aggregation logic so the result reflects the intended grain.