The Transact-SQL language has one basic statement for retrieving information from a database: the SELECT statement. With this statement, it is possible to query information from one or more tables of a database (or even from multiple databases). The result of a SELECT statement is another table, also known as a result set.
The simplest form of the SELECT statement contains a SELECT list with the FROM clause. (All other clauses are optional.) This form of the SELECT statement has the following syntax:
SELECT [ ALL |DISTINCT] column_list
FROM {table1 [tab_alias1] } ,...
table1 is the name of the table from which information is retrieved. tab_alias1 provides an alias for the name of the corresponding table. An alias is another name for the corresponding table and can be used as a shorthand way to refer to the table or as a way to refer to two logical instances of the same physical table. Don’t worry; this will become clearer as examples are presented.
column_list contains one or more of the following specifications:
- The asterisk symbol (*), which specifies all columns of the named tables in the FROM clause (or from a single table when qualified, as in table2.*)
- The explicit specification of column names to be retrieved
- The specification column_name [AS] column_heading, which is a way to replace the name of a column or to assign a new name to an expression
- An expression
- A system or an aggregate function
A SELECT statement can retrieve either columns or rows from a table. The first operation is called SELECT list (or projection), and the second one is called selection. The combination of both operations is also possible in a SELECT statement.
Example 6.1 shows the simplest retrieval form with the SELECT statement.
Get full details of all departments:
USE sample;
SELECT dept_no, dept_name, location
FROM department;
Code language: PHP (php)
The result is
dept_no | dept_name | location |
---|---|---|
d1 | Research | Dallas |
d2 | Accounting | Seattle |
d3 | Marketing | Dallas |
The SELECT statement in Example 6.1 retrieves all rows and all columns from the department table. If you include all columns of a table in a SELECT list (as in Example 6.1), you can use * as shorthand, but this notation is not recommended. The column names serve as column headings of the resulting output.
The simplest form of the SELECT statement just described is not very useful for queries. In practice, there are always several more clauses in a SELECT statement than in the statement shown in Example 6.1. The following is the syntax of a SELECT statement that references a table, with (almost) all possible clauses included:
SELECT select_list
[INTO new_table_]
FROM table
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ];
Code language: CSS (css)
The following subsections describe the clauses that can be used in a query, WHERE, GROUP BY, HAVING, and ORDER BY, as well as aggregate functions, the IDENTITY property, the new sequences feature, set operators, and the CASE expression.