Learning SQL on SQL Server 2005

Chapter 4. Joins

This chapter discusses joins --a common way to combine tables in SQL. In Chapter 2, you learned how to write simple query statements in SQL using just one table. In “real” databases, however, data is usually spread over many tables. This chapter shows you how to join tables in a database so that you can retrieve related data from more than one table. The join operation is used to combine related rows from two tables into a result set. Join is a binary operation. More than two tables can be combined using multiple join operations. Understanding the join function is fundamental to understanding relational databases, which are made up of many tables.

We start out the chapter by discussing the JOIN command. Then, we show how the same join could also be achieved with an INNER JOIN and using a WHERE clause. The concepts of the Cartesian product, equi-joins and non-equi joins, self joins, and natural joins are also introduced. We also show how multiple table joins can be performed with nested JOINs and with a WHERE clause. Finally, the concept of OUTER JOINs, with specific illustrations of the LEFT and RIGHT OUTER joins and the FULL OUTER JOIN, is also discussed.

The JOIN

In SQL Server 2005, the join is accomplished using the ANSI JOIN SQL syntax (based on ANSI Standard SQL-92), which uses the JOIN keyword and an ON clause. The ANSI JOIN syntax requires the use of an ON clause for specifying how the tables are related. One ON clause is used for each pair of tables being ...