Microsoft SQL Server 2012 Bible

Chapter 9

Merging Data with Joins, Subqueries, and CTEs

In This Chapter

Applying Relational Algebra

Building Scalable Code with Set-based Queries

Using Inner, Outer, Complex, and Q (theta) Joins

Merging Data Vertically with Unions

Understanding Subquery Types

Building Simple and Correlated Subqueries

Fitting Subqueries in the Query Puzzle

Using Common Table Expressions (CTEs)

Relational databases, by their 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 based on common 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 ...