Joins: Retrieve Data from Several Tables

A join operation compares two or more tables (or views) by specifying a column from each, comparing the values in those columns row by row, and linking the rows that have matching values. It then displays the results in a new table.

The tables specified in the join can be in the same database or in different databases.

You can state many joins as subqueries, which also involve two or more tables. 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 

When Component Integration Services is enabled, you can perform joins across remote servers. See the Component Integratin Services Users Guide.

Related concepts
Subqueries: Queries Within Other Queries