SQL Server™ 2005 Bible

Chapter 9. Merging Data with Joins and Unions

In This Chapter

• Applying relational algebra

• Building scalable code with set-based queries

• Using inner, outer, complex, and Θ (theta) joins

• Merging data vertically with unions

In my introduction to this book I said that my purpose was to share the fun of developing with SQL Server. This chapter is it. Making data twist and shout, pulling an answer out of data with a creative query, replacing a few hundred lines of slow looping VB code with a single lightning-fast set-based SQL query—it's all pure fun and covered here.

Relational databases, by their very nature, segment data into several narrow, but long, tables. Seldom does looking at a single table provide meaningful data. Therefore, merging data from multiple tables is an important task for SQL developers. The theory behind merging data sets is relational algebra, as defined by E. F. Codd in 1970.

Relational algebra consists of eight relational operators:

• Restrict: Returns the rows that meet a certain criterion

• Project: Returns selected columns, or calculated data, from a data set

• Product: Relational multiplication that returns all possible combinations of data between two data sets

• Union: Relational addition and subtraction that merges two tables vertically by stacking one table above another table and lining up the columns

• 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 ...