214
Part II: Building Databases and Working with Data
■ (^) Intersection: Returns the rows common to two data sets
■ Difference: Returns the rows unique to one data set
■ (^) Join: Returns the horizontal merger of two tables, matching up rows based on com-
mon data
■ (^) Divide: The inverse of relational multiplication, returns rows in one data set that
match every row in a corresponding data set
In addition, as a method to accomplish relational algebra, SQL has implemented the
following:
■ (^) Subqueries: Similar to a join but more fl exible; the results of the subquery are used
in place of an expression, list, or data set within an outer query.
In the formal language of relational algebra:
■ A table, or data set, is a relation or entity.
■ (^) A row is a tuple.
■ A column is an attribute.
However, this chapter uses the common terms of table, row, and column.
Relational theory is now thirty-something and has become better defi ned over the years as
database vendors compete with extensions, and database theorists further defi ne the prob-
lem of representing reality within a data structure. However, E. F. Codd’s original work is
still the foundation of relational database design and implementation.
SQL’s real power is its capability to mix and match multiple methods of selecting data. It’s
this skill in fl uidly assembling a complex query in code to accomplish what can’t be eas-
ily done with GUI tools that differentiates SQL gurus from the wannabes. You must study
embedded simple and correlated subqueries, derived tables, and common table expressions,
and then apply these query components to solve complex relational problems such as rela-
tional division.
To give credit where credit is due, much of this chapter is based on the work of E. F. Codd and C. J. Date.
Joins work with more than just tables. As listed in Chapter 6, “Introducing Basic Query
Flow,” data sources include local SQL Server tables, subqueries/CTEs, views, table-valued
user-defi ned functions, distributed data sources (other database tables), full-text search
results, and XQueries.
The reason for writing set-based queries is more than just writing elegant code. Set-based
queries scale extremely well.
c09.indd 214c09.indd 214 7/30/2012 4:25:02 PM7/30/2012 4:25:02 PM
http://www.it-ebooks.info