The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:
SELECT select_list
[INTO new_table_name]
FROM table_list
[WHERE search_conditions]
[GROUP BY group_by_list]
[HAVING search_conditions]
[ORDER BY order_list [ASC | DESC] ]
The FROM clause can also contain join specifications, which define the specific path SQL Server is to use in navigating from one table to another.
The FROM clause is also used on the DELETE and UPDATE statements to define the tables that are modified.
The WHERE clause is also used on the DELETE and UPDATE statements to define the rows in the target tables that are modified.
ORDER BY is important because relational theory specifies that the rows in a result set cannot be assumed to have any sequence unless ORDER BY is specified. ORDER BY must be used in any SELECT statement for which the order of the result set rows is important.
The clauses in a SELECT statement must be specified in the proper order.
Each reference to a database object must be unambiguous. Ambiguity can come from these sources:
SELECT *
FROM User1.TableX
SELECT *
FROM Northwind.dbo.Shippers
SELECT DISTINCT Customers.CustomerID, Customers.CompanyName
FROM Customers JOIN Orders ON
( Customers.CustomerID = Orders.CustomerID)
WHERE Orders.ShippedDate > 'May 1 1998'
This syntax becomes cumbersome when the table and view names themselves must be fully qualified. This problem is resolved by assigning a correlation name (also known as a range variable or alias) to the table using the AS keyword in the FROM clause. The fully qualified table or view name has to be specified only in the FROM clause. All other table or view references can then use the correlation name. Applying correlation names and fully qualifying the tables in the earlier sample results in this SELECT statement:
SELECT DISTINCT Cst.CustomerID, Cst.CompanyName
FROM Northwind.dbo.Customers AS Cst
JOIN
Northwind.dbo.Orders AS Ord
ON ( Cst.CustomerID = Ord.CustomerID)
WHERE Ord.ShippedDate > 'May 1 1998'
For more information about object qualification, see Using Identifiers.
Many Transact-SQL examples in the SQL Server Books Online are simplified by not using qualified names. Although these elements are left out of the examples to promote readability, it is recommended that Transact-SQL statements in production systems use qualified names.
Expressions | SELECT |