Microsoft® SQL Server® 2012 Bible

213

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)

R

elational 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 defi ned 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
c09.indd 213c09.indd 213 7/30/2012 4:25:00 PM7/30/2012 4:25:00 PM
http://www.it-ebooks.info