The SQL SELECT statement returns a result set of records, from one or more tables.[1][2]
A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data manipulation language [DML] command. As SQL is a declarative programming language, SELECT queries specify a result set, but do not specify how to calculate it. The database translates the query into a "query plan" which may vary between executions, database versions and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.
The SELECT statement has many optional clauses:
- SELECT clause is the list of columns or SQL expressions that must be returned by the query. This is approximately the relational algebra projection operation.
- AS optionally provides an alias for each column or expression in the SELECT clause. This is the relational algebra rename operation.
- FROM specifies from which table to get the data.[3]
- WHERE specifies which rows to retrieve. This is approximately the relational algebra selection operation.
- GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.
- HAVING selects among the groups defined by the GROUP BY clause.
- ORDER BY specifies how to order the returned rows.
Overview[edit]
SELECT is the most common operation in SQL, called "the query". SELECT retrieves data from one or more tables, or expressions. Standard SELECT statements have no persistent effects on the database. Some non-standard implementations of SELECT can have persistent effects, such as the SELECT INTO syntax provided in some databases.[4]
Queries allow the user to describe desired data, leaving the database management system [DBMS] to carry out planning, optimizing, and performing the physical operations necessary to produce that result as it chooses.
A query includes a list of columns to include in the final result, normally immediately following the SELECT keyword. An asterisk ["*"] can be used to specify that the query should return all columns of the queried tables. SELECT is the most complex statement in SQL, with optional keywords and clauses that include:
- The FROM clause, which indicates the table[s] to retrieve data from. The FROM clause can include optional JOIN subclauses to specify the rules for joining tables.
- The WHERE clause includes a comparison predicate, which restricts the rows returned by the query. The WHERE clause eliminates all rows from the result set where the comparison predicate does not evaluate to True.
- The GROUP BY clause projects rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause.
- The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate.
- The ORDER BY clause identifies which column[s] to use to sort the resulting data, and in which direction to sort them [ascending or descending]. Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.
- The DISTINCT keyword[5] eliminates duplicate data.[6]
The following example of a SELECT query returns a list of expensive books. The query retrieves all rows from the Book table in which the price column contains a value greater than 100.00. The result is sorted in ascending order by title. The asterisk [*] in the select list indicates that all columns of the Book table should be included in the result set.
SELECT * FROM Book WHERE price > 100.00 ORDER BY title;
The example below demonstrates a query of multiple tables, grouping, and aggregation, by returning a list of books and the number of authors associated with each book.
SELECT Book.title AS Title, count[*] AS Authors FROM Book JOIN Book_author ON Book.isbn = Book_author.isbn GROUP BY Book.title;
Example output might resemble the following:
Title Authors ---------------------- ------- SQL Examples and Guide 4 The Joy of SQL 1 An Introduction to SQL 2 Pitfalls of SQL 1Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the Book table, one could re-write the query above in the following form:
SELECT title, count[*] AS Authors FROM Book NATURAL JOIN Book_author GROUP BY title;
However, many[quantify] vendors either do not support this approach, or require certain column-naming conventions for natural joins to work effectively.
SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the select list to project data, as in the following example, which returns a list of books that cost more than 100.00 with an additional sales_tax column containing a sales tax figure calculated at 6% of the price.
SELECT isbn, title, price, price * 0.06 AS sales_tax FROM Book WHERE price > 100.00 ORDER BY title;
Subqueries[edit]
Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function. A nested query is also known as a subquery. While joins and other table operations provide computationally superior [i.e. faster] alternatives in many cases, the use of subqueries introduces a hierarchy in execution that can be useful or necessary. In the following example, the aggregation function AVG receives as input the result of a subquery:
SELECT isbn, title, price FROM Book WHERE price {begin_base_0}
SET ROWCOUNT {begin_base_0 + rows} select *, _offset=identity[int,1,1] into #temp from {table} ORDER BY {unique-key} select * from #temp where _offset > {begin_base_0} DROP TABLE #temp SET ROWCOUNT 0
SELECT * FROM [ SELECT rownum-1 as _offset, a.* FROM[ SELECT * FROM {table} ORDER BY {unique_key} ] a WHERE rownum = {begin_base_0}
Method with filter [it is more sophisticated but necessary for very big dataset][edit]
- Select only then {rows} rows with filter:
- First Page: select only the first {rows} rows, depending on the type of database
- Next Page: select only the first {rows} rows, depending on the type of database, where the {unique_key} is greater than {last_val} [the value of the {unique_key} of the last row in the current page]
- Previous Page: sort the data in the reverse order, select only the first {rows} rows, where the {unique_key} is less than {first_val} [the value of the {unique_key} of the first row in the current page], and sort the result in the correct order
- Read and send to display all the rows read from the database
select * from {table} order by {unique_key} FETCH FIRST {rows} ROWS ONLY | select * from {table} where {unique_key} > {last_val} order by {unique_key} FETCH FIRST {rows} ROWS ONLY | select * from [ select * from {table} where {unique_key} {last_val} order by {unique_key} LIMIT {rows} | select * from [ select * from {table} where {unique_key} {last_val} order by {unique_key} | select * from [ select TOP {rows} * from {table} where {unique_key} {last_val} order by {unique_key} SET ROWCOUNT 0 | SET ROWCOUNT {rows} select * from [ select * from {table} where {unique_key} Chủ Đề |