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 or LEFT [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 directive WITH (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)