SELECT (TSQL Statement)
retrieves rows from zero or more tables and sends them to the client.
Syntax
<select_xtable> ::=
SELECT [ TOP ( top_value ) ] <column_list>
[ FROM { <table_expression> } [ ,...n ] ]
[ WHERE condition ]
[ GROUP BY expression [ ,...n ] ]
[ HAVING condition ]
[ ORDER BY expression [ ,...n ] ]
[ UNION [ ALL ]
<select_xtable>
]
[...n ]
Variable assignment in SELECT:
SELECT @a = expression [ ,...n ] FROM ...
SELECT Clause
This clause contains the list of expressions making up the columns of the output rows.
SELECT [ TOP ( top_value ) ] <column_list>
<column_list> ::=
{
*
| { <table_qname> | table_alias }.*
| expression [ [ AS ] column_alias ]
| column_alias = expression
} [ ,...n ]
<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name
TOP
(top_value)- specifies the number of rows to return. top_value is an integer constant.
TOP is only allowed at the SELECT statement level, not in UNION or inside FROM clauses. - <column_list>
- list of expressions that form the columns of the output rows.
*
- is a placeholder for all columns of all tables in the
FROM
clause. - <table_qname>.
*
, table_alias.*
- is a placeholder for all columns of the specified table.
- expression
- an expression of any type. It usually contains column names of the tables in the
FROM
clause. - column_alias
- alias name for the column.
- if no column_alias is specified and expression is a table column, the name of this column is assumed. Else, the column has no name.
FROM Clause
This clause specifies the tables from which rows are retrieved.
FROM <table_expression> [ ,...n ]
<xtable> ::= <table_qname> [ [ AS ] table_alias ] [ WITH ( <table_hint> ) ]
| ( <table_expression> )
| ( <select_xtable> ) [ AS ] table_alias
<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name
<table_expression> ::= <xtable> [ <join_type> <xtable> ON <join_condition> ] [ ...n ]
<join_type> ::= [ { INNER | { LEFT [ OUTER ] } } ] JOIN
<table_hint> ::= { INDEX ( index_name ) } | { INDEX = ( index_name ) }
note: if index_name is 0, the table will not use an index.
- <xtable>
- is a single real table, or a table expression made of many tables or select expressions, joined together.
- <table_qname>
- is the qualified or unqualified name of a table.
- <table_expression>
- is a single real table, or a virtual table produced by the join of two or more <xtable>.
- <select_xtable>
- is a virtual table produced by a
SELECT
statement. It must be enclosed by(
and)
, and table_alias is mandatory. - table_alias
- specifies a name for the xtable produced by a <select_xtable>.
- <join_condition>
- boolean expression, specifying the condition of the
JOIN
. - <join_type>
- type of the
JOIN
. It can be[ INNER ] JOIN
orLEFT [OUTER] JOIN
. [ INNER ] JOIN
- is the default type of join. Only the rows for which <join_condition> is true are returned.
LEFT [OUTER] JOIN
- Same as
[ INNER ] JOIN
, but also returns the rows from the left table for which <join_condition> is false. - <table_hint>
- if the optimizer doesn’t choose the proper index, the hint directive can specify the index to use.
Most of the time, you will not use hints. But it may happen that the optimizer chooses to use an index, whereas a table scan is faster.
In this case, you can force a table scan with the directiveWITH (INDEX(0))
.
WHERE Clause
This clause filters the rows produced by the FROM
clause.
WHERE condition
- condition
- boolean expression. The rows produced by the
FROM
clause for which this condition is true are returned, the others are discarded.
GROUP BY Clause
This clause specifies the values by which the rows produced by the FROM
clause and filtered by the WHERE
clause are grouped.
GROUP BY expression [ ,...n ]
- expression
- the expression can be a column name or an expression.
HAVING Clause
This clause filters the grouped rows produced by the GROUP BY
clause.
HAVING condition
- condition
- boolean expression. The rows produced by the
GROUP BY
clause for which this condition is true are returned, the others are discarded.
ORDER BY Clause
This clause specifies the sort order for the output rows.
If expression is varchar
, a COLLATE
clause will force the sort order.
ORDER BY expression [ ,...n ]
- expression [ ,…n ]
- the rows produced by the
SELECT
statement are returned, sorted by the order specified by this list of expressions.
Descending sort order is not implemented yet.
Column_alias names are allowed in ORDER BY list.
UNION Clause
It is possible to combine the results of two or more SELECT
expression by using the UNION
clause.
Only UNION ALL
is implemented.
UNION
, which discards duplicate rows, is not implemented yet.
Variable Assignment
It is possible to assign the values of selected columns of a SELECT
statement to a list of @variables
.
In the SELECT
statement, each column expression must be preceded by @a =
, where @a
is the name of a variable.
IF SELECT RETURNS NO ROW, NO ASSIGNMENT IS DONE and THE VARIABLES RETAIN THEIR ORIGINAL VALUE !
To avoid any problem with this behaviour, it is good practice to assign NULL
to the variables before the SELECT
statement:
DECLARE @id INT
DECLARE @name VARCHAR(20)
SELECT @id = client_id, @name = name
FROM clients
WHERE client_id = 100
PRINT @id, @name -- if client 100 is found, these variables have a value
SET @id = NULL; -- reset variable value to NULL
SET @name = NULL; -- reset variable value to NULL
SELECT @id = client_id, @name = name
FROM clients
WHERE client_id = 2345
-- if client 2345 is not found, @id and @name are NULL, thanks to the SET statements.
-- Else, the values for client 100 would have been retained and printed below !
PRINT @id, @name
Remarks
The function @@ROWCOUNT
returns the number of records produced.
Permissions
The user must have select permission on all specified tables.
Examples
IF OBJECT_ID ('mytable', 'U') IS NOT NULL
DROP TABLE mytable;
IF OBJECT_ID ('another_table', 'U') IS NOT NULL
DROP TABLE another_table;
CREATE TABLE mytable (a INT NOT NULL PRIMARY KEY, b VARCHAR(50) NULL, d DATE NULL);
CREATE TABLE another_table (col1 DATE NOT NULL, col2 INT NOT NULL);
GO
INSERT INTO mytable(a, b) VALUES
(11, 'Row #1'), (12, 'Row #2'), (13, 'Row #3');
INSERT INTO another_table VALUES
('20150311', 11), ('20150312', 12), ('20150322', 12), ('20150313', 13), ('20150314', 14);
SELECT t1.a AS column_1, t1.a + 5000 AS column_2, t1.b, t2.*
FROM mytable t1, another_table t2
WHERE t1.a = t2.col2;
The result is:
column_1 |column_2 |b |col1 |col2 |
-----------+-----------+-----------------------------------+----------+-----------+
11| 5011|Row #1 |2015-03-11| 11|
12| 5012|Row #2 |2015-03-12| 12|
12| 5012|Row #2 |2015-03-22| 12|
13| 5013|Row #3 |2015-03-13| 13|
(4 row(s) affected)