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.

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.

• 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 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.
• How Joins are Structured
  A join statement, like a select statement, starts with the keyword select. The columns named after the select keyword are the columns to be included in the query results, in their desired order.
• How Joins are Processed
  Knowing how joins are processed helps to understand them—and to figure out why, when you incorrectly state a join, you sometimes get unexpected results.
• Equijoins and Natural Joins
  Joins that are based on equality (=) are called equijoins. Equijoins compare the values in the columns being joined for equality and then include all the columns in the tables being joined in the results.
• Joins with Additional Conditions
  In a join query, both the where clause and the join condition can include selection criteria.
• Joins Not Based on Equality
  The condition for joining the values in two columns does not need to be equality. You can use any of the other comparison operators: not equal (!=), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).
• Self-Joins and Correlation Names
  Joins that compare values within the same column of one table are called self-joins. To distinguish the two roles in which the table appears, use aliases, or correlation names.
• The Not-Equal Join
  A not-equal join is useful for restricting the rows returned by a self-join.
• Join More Than Two Tables
  You can join more than two tables or join more than two pairs of columns in the same statement.
• Star Joins
  A star join is a commonly used data warehouse query that runs against a star schema database, which consists of a large table (also known as a fact table) surrounded by dimension tables.
• Outer Joins
  Joins that include all rows, regardless of whether there is a matching row, are called outer joins.
• Relocated Joins
  Joins between local and remote tables can be relocated to a remote server. The remote system executes the join using a dynamically created proxy table referring back to the local table, avoiding a significant amount of network traffic.
• How Null Values Affect Joins
  Null values in tables or views being joined never match each other. Since bit columns do not permit null values, a value of 0 appears in an outer join when there is no match for a bit column in the inner table.
• Determine Which Table Columns to Join
  Use sp_helpjoins to obtain a list of the columns in two tables or views that are likely join candidates.