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 titleColumns 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 ASCNotice 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_IDName Product
-------------------------- ----------
Hansen OLa Printer
Svendson Tove NULL
Svendson Stephen Table
Svendson Stephen Chair
Pettersen Kari NULLIn 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
titlesandpublisherstables will include all publishers, even those who have no titles in thetitlestable. 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
titlesandpublishersshows 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