Zurück

Joining Tables Survival Guide


Inner join

A join that displays only the rows that have a match in both joined tables.

SELECT field1, field2, field3
  FROM first_table
  JOIN second_table ON first_table.pk = second_table.fk

Example

SELECT titles.title_id AS title_id,
       titles.title AS title,
       sales.qty AS qty
  FROM titles
  JOIN sales ON titles.title_id = sales.title_id
  ORDER BY title

Columns containing NULL do not match any values when you are creating an inner join and are therefore excluded from the result set. Null values do not match other null values.

Joining Three or More Tables

Although each join specification joins only two tables, FROM clauses can contain multiple join specifications. This allows many tables to be joined for a single query.

The titleauthor table of the pubs database offers a good example of a situation in which joining more than two tables is helpful. This Transact-SQL query finds the titles of all books of a particular type and the names of their authors:

SELECT A.au_lname,
       A.au_fname,
       T.title
  FROM authors A
  JOIN titleauthor TA ON (A.au_id = TA.au_id)
  JOIN titles T ON (TA.title_id = T.title_id)
 WHERE T.type = 'trad_cook'
ORDER BY t.title ASC

Notice that one of the tables in the FROM clause, titleauthor, does not contribute any columns to the results. Also, none of the joined columns, au_id and title_id, appear in the results. Nonetheless, this join is possible only by using titleauthor as an intermediate table.

The middle table of the join (the titleauthor table) can be called the translation table or intermediate table, because titleauthor is an intermediate point of connection between the other tables involved in the join.

When there is more than one join operator in the same statement, either to join more than two tables or to join more than two pairs of columns, the join expressions can be connected with AND or with OR.

SELECT ISNULL(komm_mitglied.komm_id,'') AS komm_id,
       ISNULL(komm_mitglied.komm_funk_id,'') AS komm_funk_id,
       ISNULL(komm_name.kname_d,'') AS kname_d,
       ISNULL(komm_name.kname_f,'') AS kname_f,
       ISNULL(komm_name.kname_i,'') AS kname_i,
       ISNULL(komm_name.name_d,'') AS name_d,
       ISNULL(komm_name.name_f,'') AS name_f,
       ISNULL(komm_name.name_i,'') AS name_i
  FROM rat_mitglied AS RA
  JOIN komm_mitglied ON (RA.parl_id = komm_mitglied.parl_id
                     AND RA.wahl_datum = komm_mitglied.parl_wahl_datum)
  JOIN komm_funk ON (komm_mitglied.komm_funk_id = komm_funk.komm_funk_id)
  JOIN unter_komm ON (komm_mitglied.komm_id = unter_komm.komm_id
                  AND komm_mitglied.unter_komm_nr = unter_komm.unter_komm_nr)
  JOIN komm_name ON (unter_komm.komm_id = komm_name.komm_id
                 AND unter_komm.unter_komm_nr = komm_name.unter_komm_nr)
   AND (komm_name.datum_bis IS NULL)
   AND (RA.parl_id = 801)
   AND (unter_komm.unter_komm_nr = 0)
   AND (komm_mitglied.datum_bis IS NULL)
   AND (RA.wahl_datum = (SELECT MAX(RB.wahl_datum)
                           FROM rat_mitglied AS RB
                          WHERE RA.parl_id = RB.parl_id))
ORDER BY komm_name.p_kname_d ASC,
         komm_mitglied.datum_von DESC

Outer join

A join that includes rows even if they do not have related rows in the joined table. You can create three variations of an outer join to specify the unmatched rows to be included:

Left outer join

All rows from the first-named table (the «left» table, which appears leftmost in the JOIN clause) are included. Unmatched rows in the right table do not appear. For example, the following SQL statement illustrates a left outer join between the Employees and Orders tables to include all Employees, even those you do not have Orders information.

Employees:

Employee_ID Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

Orders:

Prod_ID Product Employee_ID
234 Printer 01
657 Table 03
865 Chair 03

SELECT Employees.Name,
       Orders.Product
  FROM Employees
  LEFT OUTER JOIN Orders ON Employees.Employee_ID = Orders.Employee_ID

Name                       Product
-------------------------- ----------
Hansen OLa                 Printer
Svendson Tove              NULL
Svendson Stephen           Table
Svendson Stephen           Chair
Pettersen Kari             NULL

In this Example the «left» table is Employees, therfore all Rows from this Table are included even if there is no corresponding row in Orders.

Right outer join

All rows in the second-named table (the «right» table, which appears rightmost in the JOIN clause) are included. Unmatched rows in the left table are not included. For example, a right outer join between the titles and publishers tables will include all publishers, even those who have no titles in the titles table.  The resulting SQL might look like this:

SELECT titles.title_id,
       titles.title,
       publishers.pub_name
  FROM titles
  RIGHT OUTER JOIN publishers ON titles.pub_id = publishers.pub_id

Full outer join

All rows in all joined tables are included, whether they are matched or not. For example, a full outer join between titles and publishers shows all titles and all publishers, even those that have no match in the other table.

SELECT titles.title_id,
       titles.title,
       publishers.pub_name
  FROM titles
  FULL OUTER JOIN publishers ON titles.pub_id = publishers.pub_id

Correlated Subquery

A subquery that references a column in the outer statement. The inner query is executed for each candidate row in the outer statement.

SELECT DISTINCT
       phys_person.person_id AS person_id,
       ISNULL (phys_person.id_code,'') AS parl_id,
       ISNULL(CONVERT(VARCHAR(4), phys_person.datum_geburt, 102),'') AS datum_geburt,
       ISNULL(phys_person.geschlecht,'') AS geschlecht,
       person_name.name AS name,
       person_name.rufname AS vorname,
       pers_offbez.off_bez AS off_bez,
       CONVERT(VARCHAR(10), person_name.datum_von, 120) AS ersteintritt
  FROM phys_person
 
JOIN person_name ON (phys_person.person_id = person_name.person_id)
  JOIN pers_offbez ON (phys_person.person_id = pers_offbez.person_id)
  JOIN rat_mitglied AS RA ON (phys_person.person_id = RA.parl_id)
   AND (phys_person.ist_privat_haushalt = 'F' OR phys_person.ist_privat_haushalt IS NULL)
   AND (person_name.datum_bis IS NULL)
   AND (pers_offbez.datum_bis IS NULL)
   AND (RA.rat_id = 1)
   AND (RA.austritt_datum IS NULL)
   AND (RA.wahl_datum = (SELECT MAX(RB.wahl_datum)
                           FROM rat_mitglied AS RB
                          WHERE RA.parl_id = RB.parl_id))
 ORDER BY person_name.name, person_name.rufname