Chapter 8. Joins and Other Advanced Queries
Transact-SQL provides SQL Server users with a variety of advanced functionality that allows you to harness the power of a relational database. In this chapter, I describe a number of these technologies and explain how you can use them to issue powerful, compact database commands.
I begin by exploring Transact-SQL's
JOIN functionality that allows you to easily combine related data from multiple tables. I then describe several twists on the standard SQL queries: computed values, subqueries, and
CASE statements. I wrap up this chapter by taking a brief look at SQL views.
Joining Data from Multiple Tables
In the previous chapter, I describe simple queries that you can use to extract data from a single table. However, in many cases, you'll need to combine data from multiple tables to meet business requirements. Transact-SQL allows you to do this through the use of
In this section, I explain three types of
INNER JOINs allow you to match related records from different tables.
OUTER JOINs also include records from one or both tables that do not have corresponding record(s) in the other table.
Self-joins are a special case in which you join a table with itself to compare records in the same table.
Matching records with INNER JOINs
The most common type of
JOIN statement is the
INNER JOIN. This statement, also known as an equi-join, combines records from two tables that have one or more specified attributes in common. For ...