Transact-SQL User's Guide

Home

Chapter 4 Joins: Retrieving Data from Several
Tables

Transact-SQL User's Guide

Chapter 4 Joins: Retrieving Data from Several Tables

How joins work

When you join two or more tables, the columns being compared must have similar values--that is, values using the same or similar datatypes.

There are several types of joins, such as equijoins, natural joins, and outer joins. The most common join, the equijoin, is based on equality. The following join finds the names of authors and publishers located in the same city:

select au_fname, au_lname, pub_name 
from authors, publishers 
where authors.city = publishers.city

au_fname      au_lname      pub_name 
--------      --------      -------------------- 
Cheryl        Carson        Algodata Infosystems 
Abraham       Bennet        Algodata Infosystems 
 
(2 rows affected)

Because the query draws on information contained in two separate tables, publishers and authors, you need a join to retrieve the requested information. This statement joins the publishers and authors tables using the city column as the link.:

where authors.city = publishers.city

Join syntax

You can embed a join in a select, update, insert, delete, or subquery. Other join restrictions and clauses may follow the join conditions. Joins use the following syntax:

start of select, update, insert, delete,
or subquery 
     from {table_list | view_list} 
     where [not] 
          [table_name. | view_name.]column_name join_operator 
          [table_name. | view_name.]column_name
     [{and | or} [not]
          [table_name.|view_name.]column_name join_operator 
           [table_name.|view_name.]column_name]...

End of select, update, insert, delete, or
subquery

Joins and the relational model

The join operation is the hallmark of the relational model of database management. More than any other feature, the join distinguishes relational database management systems from other types of database management systems.

In structured database management systems, often known as network and hierarchical systems, relationships between data values are predefined. Once a database has been set up, it is difficult to make queries about unanticipated relationships among the data.

In a relational database management system, on the other hand, relationships among data values are left unstated in the definition of a database. They become explicit when the data is manipulated--when you query the database, not when you create it. You can ask any question that comes to mind about the data stored in the database, regardless of what was intended when the database was set up.

According to the rules of good database design, called normalization rules, each table should describe one kind of entity--a person, place, event, or thing. That is why, when you want to compare information about two or more kinds of entities, you need the join operation. Relationships among data stored in different tables are discovered by joining them.

A corollary of this rule is that the join operation gives you unlimited flexibility in adding new kinds of data to your database. You can always create a new table that contains data about a different kind of entity. If the new table has a field with values similar to those in some field of an existing table or tables, it can be linked to those other tables by joining.


	Expand Search