Selecting Data with Transact-SQL

To retrieve data from a table, you use the SELECT Transact-SQL statement. This page discusses how to retrieve data from a single table. The following two lessons show you how to use joins to retrieve data from multiple tables. You can specify the names of specific columns from which to request data, or you can request data from all columns in the table.
The SELECT statement uses the following general syntax:

SELECT select_list
[INTO new_table_name]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC|DESC]]

SELECT select_list
[INTO new_table_name]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC|DESC]]

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.

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;

For example, to select all data from the employees table for everyone with the last name Smith, you would specify that you want every column returned by using the * wildcard with the following Transact-SQL statement:

SELECT *
FROM employees
WHERE LastName = 'Smith'

Alternatively, you could achieve the same result by explicitly specifying every column name in the table, like this:

SELECT EmployeeID, LastName, FirstName, 
AddressLine1, AddressLine2, City, 
State, Zip, HomePhone, HomeFax  
FROM employees
WHERE LastName = 'Smith'

What if you only wanted to return only specific columns?
You explicitly list the columns that you wish to see. For example, if you want to return only the FirstName, and HomePhone for anyone with the last name of Smith, you would use this Transact-SQL statement:

SELECT FirstName, HomePhone  
FROM employees
WHERE LastName = 'Smith'

• Interacting Through the Query Window:
  This part of the Management Studio takes the place of what was, at one time, a separate tool that was called Query Analyzer. The Query window is your tool for interactive sessions with a given SQL Server. It is where you can execute statements using Transact-SQL (T-SQL). I pronounce it Tee-Squeal, but it is supposed to be Tee-Sequel. T-SQL is the native language of SQL Server and its a dialect of Structured Query Language (SQL), and is largely compliant withmodern ANSI/ISO SQL standards. You will find that most RDBMS products support basic ANSI/ISO SQL compatibility. Because the Query window is where you will spend a fair amount of time, let us take a more in-depth look at this tool and get familiar with how to use it. In the next lesson, you will learn how joins are used to retrieve data from multiple tables.