Select (SQL)

The SQL SELECT statement returns a result set of rows, from one or more tables. 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 list is the list of columns or SQL expressions to 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 list. This is the relational algebra rename operation. * FROM specifies from which table to get the data. * 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.…

The SQL SELECT statement returns a result set of rows, from one or more tables. 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 list is the list of columns or SQL expressions to 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 list. This is the relational algebra rename operation. FROM specifies from which table to get the data. 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. 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 1 Under 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 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; SELECT isbn, title, price FROM Book WHERE price A subquery can use values from the outer query, in which case it is known as a correlated subquery. Since 1999 the SQL standard allows WITH clauses, i.e. named subqueries often called common table expressions (named and designed after the IBM DB2 version 2 implementation; Oracle calls these subquery factoring). CTEs can also be recursive by referring to themselves; the resulting mechanism allows tree or graph traversals (when represented as relations), and more generally fixpoint computations. SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm FROM Book b JOIN (SELECT SUM(Items_Sold) Items_Sold, Company_Nm, ISBN FROM Book_Sales GROUP BY Company_Nm, ISBN) sales ON sales.isbn = b.isbn |align="center"| |- |align="center"| || |align="center"| |- |align="center"| || |align="center"| |- |align="center"| || |align="center"| |- |align="center"|does not exist || |align="center"| |} Given a table T, the query will result in all the elements of all the rows of the table being shown. With the same table, the query will result in the elements from the column C1 of all the rows of the table being shown. This is similar to a projection in relational algebra, except that in the general case, the result may contain duplicate rows. This is also known as a Vertical Partition in some database terms, restricting query output to view only specified fields or columns. With the same table, the query will result in all the elements of all the rows where the value of column C1 is '1' being shown in relational algebra terms, a selection will be performed, because of the WHERE clause. This is also known as a Horizontal Partition, restricting rows output by a query according to specified conditions. With more than one table, the result set will be every combination of rows. So if two tables are T1 and T2, will result in every combination of T1 rows with every T2 rows. E.g., if T1 has 3 rows and T2 has 5 rows, then 15 rows will result. Although not in standard, most DBMS allows using a select clause without a table by pretending that an imaginary table with one row is used. This is mainly used to perform calculations where a table is not needed. The SELECT clause specifies a list of properties (columns) by name, or the wildcard character (“*”) to mean “all properties”. SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number, columns FROM tablename ) AS foo WHERE row_number can be non-deterministic: if sort_key is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where sort_key is the same. When sort_key is unique, each row will always get a unique row number. SELECT * FROM ( SELECT RANK() OVER (ORDER BY age ASC) AS ranking, person_id, person_name, age FROM person ) AS foo WHERE ranking The above code could return more than ten rows, e.g. if there are two people of the same age, it could return eleven rows. SELECT * FROM T FETCH FIRST 10 ROWS ONLY This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c and Mimer SQL. Microsoft SQL Server 2008 and higher supports FETCH FIRST , but it is considered part of the ORDER BY clause. The ORDER BY , OFFSET , and FETCH FIRST clauses are all required for this usage. SELECT * FROM T ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY {rows} = Number of rows in a page {page_number} = Number of the current page {begin_base_0} = Number of the row - 1 where the page starts = (page_number-1) * rows Select * from {table} order by {unique_key} select 1 a, 1 b union all select 1, 2 union all select 1, 3 union all select 2, 1 union all select 5, 1 SQL Server 2008 supports the "row constructor" feature, specified in the SQL:1999 standard select * from (values (1, 1), (1, 2), (1, 3), (2, 1), (5, 1)) as x(a, b)

Text derived from https://en.wikipedia.org/w/index.php?oldid=1351767052 under CC BY-SA 4.0; modifications have been made.