112
Part II: Building Databases and Working with Data
Access and who have built queries using only the query interface available in Access are amazed
when they understand the enormous power and capabilities of the full SQL query language.This chapter builds a single table query structure and establishes the logical query execu-
tion order critical for developing basic or advanced queries. With this foundation in place,
the rest of Part II, “Building Databases and Working with Data,” develops the basic SELECT
into the most elegant, fl exible, and powerful command in all of computing.Understanding Query Flow
You can think about query fl ow in four different ways. The fi rst is to imagine the query
using a logical fl ow. Some developers, on the other hand, think through a query visually
using the layout of SQL Server Management Studio’s Query Designer. The third approach is to
syntactically view the query. You can view the query in a specifi c fi xed order:
SELECT – FROM – WHERE – GROUP BY – HAVING – ORDER BY. Finally, to illustrate the
declarative nature of SQL, the fourth way to think about the query fl ow — the actual physi-
cal execution of the query — is to execute in the most effi cient order depending on the data
mix and the available indexes.Syntactical Flow of the Query Statement
In its basic form, the SELECT statement tells SQL Server what data to retrieve, including
which columns, rows, and tables to pull from, and how to sort the data. The order of the
clauses in the SELECT statement are signifi cant; however, they process in an order different
from how they are syntactically specifi ed, which this chapter discusses later.Following is an abbreviated syntax for the SELECT command:SELECT
[DISTINCT][TOP (n)] *, columns, or expressions
[FROM data source(s)]
[JOIN data source
ON condition](may include multiple joins)
[WHERE conditions]
[GROUP BY columns]
[HAVING conditions]
[ORDER BY Columns];
The SELECT statement begins with a list of columns or expressions. You need at least one
expression — everything else is optional. An integer, scalar function, or a string value
encapsulated in single quotes can represent the expression. The simplest possible valid
SELECT statement is as follows:SELECT 1;If you do not supply a FROM clause, SQL Server returns a single row with values. (Oracle
requires a FROM DUAL to accomplish the same thing.)c06.indd 112c06.indd 112 7/30/2012 4:15:56 PM7/30/2012 4:15:56 PM
http://www.it-ebooks.info