SELECT (SQL statement)

SELECT (SQL statement)
Name	SELECT
Type	SQL statement
Introduced	1974
Standard	SQL
Developer	International Organization for Standardization
Related	INSERT, UPDATE, DELETE, JOIN, WHERE

SELECT (SQL statement) The SELECT statement is a declarative Structured Query Language command used to retrieve rows from one or more relational database tables or views. It originated from the relational model work of Edgar F. Codd and was standardized by ANSI and ISO committees, becoming central to systems like Oracle Database, Microsoft SQL Server, MySQL, PostgreSQL, and SQLite. SELECT enables projection, filtering, aggregation, sorting, and composition of datasets for applications ranging from transaction processing to data warehousing and business intelligence.

Overview

SELECT expresses a query as a specification of desired columns and rows from table-like objects such as views or materialized views. Implementations in IBM systems and in commercial products by Oracle Corporation, Microsoft Corporation, and SAP SE extend the core ISO/IEC 9075 standard with vendor-specific functions, windowing features, and optimization hints. In practice SELECT is used by developers building applications on platforms including Linux, Windows NT, and macOS and integrated into tools like Tableau (software), Power BI, and Apache Spark.

Syntax and clauses

A canonical SELECT includes clauses such as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET. Clause semantics were refined in standards maintained by ISO and influenced by academic work from E. F. Codd. The FROM clause names relations (tables, views, subquery-derived tables) possibly qualified by schema names from systems like PostgreSQL or Oracle Database. WHERE applies predicate filtering, GROUP BY defines aggregation grouping often combined with HAVING for post-aggregation filters, and ORDER BY specifies a sort order used by client tools such as Oracle SQL Developer and Microsoft SQL Server Management Studio.

Query types and modifiers

SELECT supports projection, selection, aggregation, window functions, DISTINCT, TOP/FETCH, and INTO for result materialization. DISTINCT removes duplicates as specified in standards adopted by ANSI; TOP and FETCH FIRST are vendor-specific variants used by Microsoft SQL Server and DB2 respectively. Window functions introduced in later standards (ROW_NUMBER, RANK) are implemented in PostgreSQL, Oracle Database, and SQL Server to enable running totals and ranking analyses common in data warehousing and reporting.

Joins and set operations

Joins (INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS) combine rows from two or more relations; these are implemented in systems from IBM to MariaDB. Natural joins and USING clauses emerged from academic proposals and standards work referenced by Ted Codd-inspired research. Set operations UNION, INTERSECT, and EXCEPT/ MINUS perform multiset algebra on SELECT results; behavior varies among Oracle Corporation, Microsoft Corporation, and MySQL implementations especially with regard to duplicates and ORDER BY placement.

Subqueries and derived tables

Subqueries in WHERE, FROM, and SELECT allow nested queries and correlated evaluation, drawing on theoretical foundations in relational algebra developed at institutions like IBM Research and Bell Labs. Derived tables (subquery aliases) and common table expressions (WITH) facilitate modular query design adopted by PostgreSQL, SQL Server, and SQLite. Correlated subqueries reference outer query columns and influence optimizer strategies perfected in commercial engines such as Oracle Database and SQL Server.

Performance and optimization

Query planners and optimizers in systems like Oracle Database, PostgreSQL, MySQL, and SQL Server transform SELECT statements into efficient execution plans using indexes, statistics, and cost models. Techniques include predicate pushdown, join reordering, use of B-tree or hash index structures, materialized views, and partition pruning in data warehouses used by Snowflake (company) and Amazon Redshift. Profiling tools from vendors and open-source projects (EXPLAIN, ANALYZE) help diagnose slow queries; DBAs at organizations such as Facebook, Twitter, and Google apply query tuning, denormalization, and caching to meet scale.

Examples and common usage patterns

Common patterns include simple projection: SELECT column FROM table; filtered selection with WHERE predicates used in applications built on Microsoft .NET Framework or Java Platform, Enterprise Edition; aggregation with GROUP BY for reporting dashboards in Tableau (software); JOIN patterns to assemble normalized data modeled after Codd’s normalization principles; and use of window functions for time series analytics in platforms such as Apache Kafka-backed pipelines. Vendors provide example code in documentation for Oracle Corporation, Microsoft Corporation, Amazon Web Services, and open-source communities like PostgreSQL Global Development Group.

Category:Structured Query Language