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
The introduction to this book stated 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 languishing row-by-row iterative code with a single blazingly 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, ...