T-SQL Basics: Anatomy of the Select Statement

Relational databases are important not only because of the information they store but, more importantly, for the data we retrieve from them. The select statement allows us to ask the database a question. It’s the way we retrieve information from the database system.

There are two standards that govern SQL (Structured Query Language) — ANSI (American National Standards Institute) and ISO (International Standards Organization). Microsoft SQL Server supports both of these standards. Like other database products, Microsoft SQL Server has its own dialect of SQL. This dialect for SQL Server is called T-SQL or Transact-SQL.

The basics of the select statement are the same from one database to another. However, some options of the statement vary from product to product. We’ll specifically examine SQL Server’s select statement, although it is virtually identical to other select statements.

SQL Server is one of many relational database systems. Relational databases store information in a series of tables (rows and columns) that are “related” to each other.

Let’s say we have a customer database. This database has a few different sub-topics — or entities like customer, order, vendor, product, and employee. Entities can be translated to the topic of the table. We’ll only work with a single table to keep our examples very simple.

Our table has the following structure and records (rows):

CUSTOMER

CustomerID	LastName	FirstName	Address	City	State	Zip
1000	Adams	Susan	101 Main Street	Cary	NC	27513
1001	Johnson	JoAnne	5402 Loop 1	Austin	TX	78752
1002	Smith	Ron	2201 Thomas Dr	Panama City	FL	32401
1003	Baker	Pete	2408 NW 119th	Oklahoma City	OK	73120
1004	Smith	Bill	4407 12th Avenue	Austin	TX	78746

Let’s begin by discussing the parts of the select statement. Then we will select information from one table only.

The select statement is comprised of multiple clauses. These are:

SELECT
FROM
WHERE
GROUP BY
ORDER BY
HAVING

Each of these clauses has information that follows it. These are:

SELECT column(s)
FROM table / view
WHERE condition / expression
GROUP BY column(s)
ORDER BY column(s)
HAVING aggregate function and condition / expression

The only two clauses that are required in a select statement are SELECT and FROM.

The SELECT clause identifies which columns we want to retrieve, and the FROM clause identifies the name of the table we are using. We might have something like this:

Select lastname, firstname
from customer

This gives us the output:

LastName	FirstName
Adams	Susan
Johnson	JoAnne
Smith	Ron
Baker	Pete
Smith	Bill

Notice that the headings above the column data are not title case as they are in the table. This is because my query had them listed as lower case. Our select statement indicates exactly what we want to see in the output.

Now we’ll review the optional clauses.

WHERE

The WHERE clause is a filter for the query results and uses a condition or expression. We use the WHERE clause to specify our request for only particular rows.

If I want to retrieve only those individuals with the last name of Smith then I can add a WHERE clause.

Select lastname, firstname
from customer
where lastname = ‘Smith’

This gives us the output:

CustomerID	LastName	FirstName	Address	City	State	Zip
1002	Smith	Ron	2201 Thomas Dr	Panama City	FL	32401
1004	Smith	Bill	4407 12th Avenue	Austin	TX	78746

GROUP BY

The purpose of the GROUP BY clause is to group or cluster records together based on a column or list of columns. In our example, I also use an aggregate function that counts the number of rows specifying our criteria. I also created an alias for the heading of the column with the function.

Select State, count(state) as ‘Count of Customers by State’
from customer
group by state

This gives us the output:

State	Count of Customers by State
FL	1
NC	1
OK	1
TX	2

HAVING

The HAVING clause works in conjunction with the GROUP BY clause. It’s very much like the WHERE clause since it works as a filter. However, it works as a filter on the aggregate group.

Select State, count(state) as ‘Count of Customers Greater Than 1’
from customer
group by state
having count(state) > 1

This gives us the output:

State	Count of Customers Greater Than 1
TX	2

ORDER BY

The ORDER BY clause indicates the sort order for the query results.

If I want to put my results in alphabetical order by lastname, I can add an ORDER BY clause.

Select lastname, firstname
from customer
order by lastname

This gives us the output:

LastName	FirstName
Adams	Susan
Baker	Pete
Johnson	JoAnne
Smith	Ron
Smith	Bill

Notice that Ron Smith appears before Bill Smith in our example even though “Bill” comes before “Ron” alphabetically. This is because I didn’t order by firstname, only by lastname. To correct this, I can do the following.

Select lastname, firstname
from customer
order by lastname, firstname

This gives us the output:

LastName	FirstName
Adams	Susan
Baker	Pete
Johnson	JoAnne
Smith	Bill
Smith	Ron

We saw the basics of selecting data from a single table. Look for Part II where we’ll explore querying from multiple tables.

 

Related Courses
Writing Queries Using Microsoft SQL Server 2008 Transact-SQL
SQL Server 2008 R2 for Administration
Designing and Implementing a SQL Server 2008 R2 Database