Microsoft® SQL Server® 2008 For Dummies®

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 JOIN statements.

In this section, I explain three types of JOIN statements:

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