Select Data  «Prev 

Overview of selecting data

SELECT Statement syntax

Getting Started with a Basic Select Statement

If you have not used SQL before, the following section will discuss DML in more detail.
The SELECT statement and the structures used within it contain most of the commands you will perform with SQL Server. Let us take a look at the basic syntax rules for a SELECT statement: 

SELECT [ALL|DISTINCT] [TOP (<expression>) [PERCENT] [WITH TIES]]
<column list>
[FROM <source table(s)/view(s)>]
[WHERE <restrictive condition>]
[GROUP BY <column name or expression using a column in the SELECT list>]
[HAVING <restrictive condition based on the GROUP BY results>]
[ORDER BY <column list>]
[[FOR XML {RAW|AUTO|EXPLICIT|PATH [(<element>)]}[, XMLDATA]
[, ELEMENTS][, BINARY base 64]]
[OPTION (<query hint>, [, ...n])]

Let us look at the individual elements. Note that the parentheses around the TOP expression are, technically speaking, optional. Microsoft refers to them as required, and then points out that a lack of parentheses is actually supported, but for backward compatibility only. This means that Microsoft may pull support for that in a later release, so if you do not need to support older versions of SQL Server, I strongly recommend using parentheses to delimit a TOP expression in your queries.

SELECT Statement and FROM Clause

The verb, in this case a SELECT, is the part of the overall statement that tells SQL Server what you are doing. A SELECT indicates that you are merely reading information, as opposed to modifying it. What you are selecting is identified by an expression or column list immediately following the SELECT. Next, you add in more specifics, such as where SQL Server can find this data. The FROM statement specifies the name of the table or tables from which you want to get your data. With these, you have enough to create a basic SELECT statement. Start the SQL Server Management Studio and take a look at a simple SELECT statement: 

SELECT * FROM INFORMATION_SCHEMA.TABLES;

select_list is either a comma separated list of columns or the * wildcard character. Also, an expression or calculation can be in the select list, such as ItemQty * 5. Additionally, a string literal or constant expression can be in the select list.
1) select_list is either a comma separated list of columns or the * wildcard character. Also, an expression or calculation can be in the select list, such as ItemQty * 5. Additionally, a string literal or constant expression can be in the select list.

new_table_name is the name of the new table to create with the results of the select statement.
2) new_table_name is the name of the new table to create with the results of the select statement.

table_source is the name of the table from which you’re requesting data.
3) table_source is the name of the table from which you are requesting data.

search_condition is the way you specify the actual rows in the table that are to be retrieved. If you omit the WHERE clause, every row in the table will be retrieved. So specifying a WHERE clause in your queries is generally a good idea.
4) search_condition is the way you specify the actual rows in the table that are to be retrieved. If you omit the WHERE clause, every row in the table will be retrieved. So specifying a WHERE clause in your queries is generally a good idea.

Group_by_expression is an expression or set of columns that are used with aggregate data. For more information, see lesson on Aggregated data later in this module.
5) Group_by_expression is an expression or set of columns that are used with aggregate data.

search_condition is similar to the WHERE clause search_condition, except that it is used for aggregate data.
6) search_condition is similar to the WHERE clause search_condition, except that it is used for aggregate data.

Order_expression specifies one or more columns used to sort data.
7) Order_expression specifies one or more columns used to sort data.