Selecting Data with Transact-SQL

To retrieve data from a table, you use the SELECT Transact-SQL statement. This page covers retrieving 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 to request data from, or 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]]

Each part in square brackets ([]) is optional. Taken one at a time:

1. select_list is either a comma-separated list of columns or the * wildcard character. An expression or calculation can appear in the select list too, such as ItemQty * 5, and so can a string literal or constant expression.
2. new_table_name is the optional name of a new table to create with the results of the SELECT statement, when the INTO clause is used.
3. table_source is the name of the table from which you are requesting data.
4. search_condition (in WHERE) specifies the actual rows in the table to be retrieved. If you omit the WHERE clause, every row in the table is retrieved, so specifying a WHERE clause is generally a good idea.
5. group_by_expression is an expression or set of columns used with aggregate data. The lesson on aggregated data later in this module covers this in more depth.
6. search_condition (in HAVING) is similar to the WHERE clause's search condition, except that it filters aggregate data (the groups themselves) rather than individual rows.
7. order_expression specifies one or more columns used to sort the results, optionally ascending (ASC) or descending (DESC).

If you have not used SQL before, this section covers the full syntax in a bit more detail. The SELECT statement and the structures used within it make up most of what you will do with SQL Server. Here are the fuller 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 BASE64] | JSON {AUTO|PATH} [, ROOT] [, INCLUDE_NULL_VALUES] [, WITHOUT_ARRAY_WRAPPER]}]
[OPTION (<query hint> [, ...n])]

A closer look at a few of these elements:

The parentheses around the TOP expression are, technically speaking, optional, if you are only supplying a plain numeric constant. Microsoft documents them as required, then notes that a lack of parentheses is actually still supported, but for backward compatibility only. That means Microsoft could remove support for the bracket-less form in a later release, so unless you specifically need to support older SQL Server versions, it is worth getting in the habit of always using parentheses around a TOP expression.

Two formatting options worth knowing exist at the end of this syntax: FOR XML, for converting the result set directly into XML, and FOR JSON, which does the same thing for JSON. FOR JSON is the more commonly reached-for option in modern application development, since JSON is the data-interchange format most web and mobile clients expect, and it works much the same way as FOR XML: AUTO mode infers the shape of the output from your query, while PATH mode lets you control the output structure explicitly.

The verb, in this case SELECT, is the part of the statement that tells SQL Server what you are doing. A SELECT indicates that you are reading information, not modifying it. What you are selecting is identified by an expression or column list immediately following the SELECT keyword. The FROM clause specifies the name of the table or tables you want the data from. With these two pieces, you have enough for a basic SELECT statement. Open SQL Server Management Studio and try a simple one:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

A slightly more realistic example, filtered with a WHERE clause and sorted with ORDER BY:

SELECT CustomerID, OrderDate
FROM Sales.Orders
WHERE OrderDate >= '2025-01-01'
ORDER BY OrderDate DESC;

If this query ran inside a stored procedure, you would typically start the procedure with SET NOCOUNT ON, which suppresses the "n rows affected" message SQL Server would otherwise send back after each statement, keeping the output clean for whatever is consuming the result set.

To select all data from the employees table for everyone with the last name Smith, you could return every column using the * wildcard:

SELECT *
FROM employees
WHERE LastName = 'Smith'

Alternatively, you could achieve the same result by explicitly naming every column in the table:

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

What if you only wanted specific columns back? Just list the ones you want. To return only the FirstName and HomePhone for anyone with the last name Smith:

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

This part of SQL Server Management Studio takes the place of what was, at one time, a separate tool called Query Analyzer. The Query window is your tool for interactive sessions with a given SQL Server: it is where you execute statements using Transact-SQL, T-SQL for short. I pronounce it Tee-Squeal, but it is supposed to be Tee-Sequel. T-SQL is the native language of SQL Server, a dialect of Structured Query Language that is largely compliant with modern ANSI/ISO SQL standards; most RDBMS products support at least basic ANSI/ISO SQL compatibility. Because the Query window is where you will spend a fair amount of time, it is worth getting comfortable with it early on.

A short recap before moving on:

• The SELECT statement retrieves data from a table, built from up to seven optional clauses: the select list, an optional INTO, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.
• Omitting WHERE retrieves every row in the table, which is rarely what you actually want outside of small reference tables.
• GROUP BY and HAVING work together for aggregate data; the rest of this module's lessons on aggregated queries go into that pairing in depth.
• The fuller syntax adds TOP, DISTINCT, and formatting the result set directly as FOR XML or FOR JSON, useful when an application expects structured output rather than a plain result set.
• The next two lessons build on this single-table foundation to cover joins across multiple tables.

In the next lesson, you will learn how joins are used to retrieve data from multiple tables.