Databases Reference
In-Depth Information
In Access, we can define relationships between tables. However, these
relationships have no effect on SQL statements. Thus, an INNER JOIN statement
does not require that a relationship already exist between the participating tables.
Relationships are used in Design View, however, and translate into INNER JOIN
statements. For example, if we add BOOKS and PUBLISHERS to the Query
Design View window, move Title and PubName to the Design grid, and then
view the SQL equivalent, we will see an INNER JOIN clause in the SQL
statement.
•
6.7.3 The SELECT Statement
The SELECT statement is the workhorse of SQL commands (as you can tell by the
length of our discussion on this statement). The statement returns a table and can perform
both of the relational algebra operations
selection
and
projection
. The syntax of the
SELECT statement is:
SELECT [predicate] ReturnColumnDescription,...
FROM TableExpression
[WHERE RowCondition]
[GROUP BY GroupByCriteria]
[HAVING GroupCriteria]
[ORDER BY OrderByCriteria]
Let us describe the various components of this statement. We note immediately that the
keyword SELECT is in some ways unfortunate, since it denotes the relational algebra
operation of projection, not selection. It is the WHERE clause that performs selection.
6.7.3.1 Predicate
The
predicate
is used to describe how to handle duplicate return rows. It can have one of
the following values: ALL, DISTINCT, DISTINCTROW, or TOP.
The default option ALL returns all qualifying rows, including duplicates. If there is more
than one qualifying row with the same values in all of the columns that arerequested in
the ReturnColumnDescription, then the option DISTINCT returns only the first such row.
The:
TOP
number
or:
TOP
percent
PERCENT
option returns the top
number
(or percent) of rows in the sort order determined by the
ORDER BY clause.
The DISTINCTROW option can be a bit confusing, so let us see if we can straighten it
out. The Access Help system says that the DISTINCTROW option “Omits data based on
