The SQL clause JOIN logically relates multiple table or view sources so as to retrieve a single result set (ie a joined table). The JOIN clause is used in the FROM clause to create joined tables.

Basic Syntax

The basic syntax is as follows:

FROM TableLeft [JOINType] JOIN TableRight [ON (JoinSearchCondition)]

The JOINType can be one of three types. For the three types of joins there are three syntaxes:

Some consider self-joins to be a fourth kind of join, but self-joins are really just special cases of the three joins. A self-join is where a table is joined with an alias of itself, just as if the alias were a different table altogether. A self-join can be done with all three types of joins. When performing self-joins, it is actually preferable to use two aliases and to qualify all columns.

The ON clause makes the JOIN clause utilize the JoinSearchCondition to compare rows for matching.

The JoinSearchCondition defines how rows will be compared. Here are some notes on JoinSearchCondition:

A joined table can be joined with another table or joined table using the following syntax:

FROM TableLeft [JOINType] JOIN TableRight ON (JoinSearchCondition1)
[[JOINType] JOIN TableRight2 ON (JoinSearchCondition2)]

Earlier versions of SQL allowed the *= and =* operators to make left and right outer joins respectively in the WHERE clause. That made it ambiguous as to whether the join or filter should be applied first. SQL-92 and later versions of SQL Server prefer the all joins be done in the FROM clause, and all filters be done in the WHERE and HAVING clauses.

EGs

All 3 kinds of JOINs

These SELECT commands demonstrate the inner, outer (left, right, and full), and cross commands.

Assume that the tables have this data:

CREATE TABLE authors (au_name char(99), city char(99));
    INSERT authors VALUES ('Cindy', 'Chicago');
    INSERT authors VALUES ('Dan',   'Dallas');
    INSERT authors VALUES ('Neil',  'New York');
CREATE TABLE publishers (city char(99), pub_name char(99));
    INSERT authors VALUES ('Chicago',  'C Books');
    INSERT authors VALUES ('Miami',    'M Books');
    INSERT authors VALUES ('New York', 'N Books');
SELECT a.au_name, a.city, p.city, p.pub_name
FROM authors AS a
INNER JOIN publishers AS p ON (a.city = p.city)
ORDER BY a.city, p.city
/* Returns 2 joined a & p rows that match:
Cindy, Chicago, Chicago, C Books
Neil, New York, New York, N Books */
SELECT a.au_name, a.city, p.city, p.pub_name
FROM authors AS a
LEFT JOIN publishers AS p ON (a.city = p.city)
ORDER BY a.city, p.city
/* Returns 2 joined a & p rows that match, 
plus 1 row from a that didn't match:
Cindy, Chicago, Chicago, C Books 
Dan, Dallas, NULL, NULL 
Neil, New York, New York, N Books */
SELECT a.au_name, a.city, p.city, p.pub_name
FROM authors AS a
RIGHT JOIN publishers AS p ON (a.city = p.city)
ORDER BY a.city, p.city
/* Returns 2 joined a & p rows that match, 
plus 1 row from p that didn't match: 
Cindy, Chicago, Chicago, C Books 
NULL, NULL, Miami, M Books 
Neil, New York, New York, N Books */
SELECT a.au_name, a.city, p.city, p.pub_name
FROM authors AS a
FULL JOIN publishers AS p ON (a.city = p.city)
ORDER BY a.city, p.city
/* Returns 2 joined a & p rows that match, 
plus 1 row from a that didn't match, 
plus 1 row from p that didn't match: 
Cindy, Chicago, Chicago, C Books 
Dan, Dallas, NULL, NULL 
NULL, NULL, Miami, M Books 
Neil, New York, New York, N Books */
SELECT a.au_name, a.city, p.city, p.pub_name
FROM authors AS a
CROSS JOIN publishers AS p --ON (a.city = p.city) --No ON clause needed for cross joins.
ORDER BY a.city, p.city
/* Returns 9 rows = a rows * p rows = 3*3: 
Cindy, Chicago, Chicago, C Books 
Cindy, Chicago, Miami, M Books 
Cindy, Chicago, New York, N Books 
Dan, Dallas, Chicago, C Books 
Dan, Dallas, Miami, M Books 
Dan, Dallas, New York, N Books 
Neil, New York, Chicago, C Books 
Neil, New York, Miami, M Books 
Neil, New York, New York, N Books */

JOIN and WHERE equivalent

These two SELECT commands produce equivalent results but the former is preferred. Each command joins two tables by the SupplierID column of each table. 

SELECT Pr,ProductID, Su.SupplierID, Su.CompanyName
FROM Suppliers AS Su
JOIN Products AS Pr ON (Su.SupplierID = Pr.SupplierID)
WHERE Pr.UnitPrice < $10
      AND Su.CompanyName LIKE 'Geo%'
SELECT Pr.ProductID, Su.SupplierID, Su.CompanyName
FROM Suppliers AS Su, Products AS Pr
WHERE Su.SupplierID = Pr.SupplierID
      AND Pr.UnitPrice < $10
      AND Su.CompanyName LIKE 'Geo%'

Expressions in or out of JOIN

These two SELECT commands appear similar, but return different results. Up to the comment in each SELECT command, the result set includes all rows from table authors because of the left outer join. Note also how the first example is a bit more explicit because the columns in the SelectList were qualified.

SELECT A.au_fname, A.state, TA.title_id
FROM authors AS A
LEFT JOIN titleauthor AS TA
ON (A.au_id = TA.au_id AND A.state <> 'ca')
/*This SELECT command will show NULLs for titles by authors from California.*/
SELECT au_fname, state, title_id
FROM authors AS A
LEFT JOIN titleauthor AS TA
ON (A.au_id = TA.au_id)
/*Titles by authors from California are eliminated from the final result set because of the WHERE clause.*/
WHERE A.state <> 'ca'

FULL JOIN to items missing from 2nd table

This SELECT command uses the FULL JOIN to see which Patients have no Transactions. To get which Patients have Transactions, simply change the WHERE clause to WHERE patientid IS NOT NULL.

SELECT DISTINCT pid, patientid, fname, lname 
FROM pat FULL JOIN tra on pat.pid = tra.patientid 
WHERE patientid IS NULL 
ORDER BY pid

Self JOIN

These SELECT command with self-joined inner joins are used to list duplicates values of one column, and their corresponding different values in another column.

SELECT DISTINCT TA1.au_id, TA1.title_id
FROM titleauthor AS TA1
INNER JOIN titleauthor AS TA2 ON (
    TA1.au_id = TA2.au_id
    AND TA1.title_id <> TA2.title_id)
ORDER BY TA1.au_id
/*The result set will list authors and their multiple titles.*/
SELECT DISTINCT TA1.title_id, TA1.au_id
FROM titleauthor AS TA1
INNER JOIN titleauthor AS TA2
ON (TA1.title_id = TA2.title_id
    AND TA1.au_id <> TA2.au_id)
ORDER BY TA1.title_id
/*The result set will list titles and their multiple authors.*/

Non-equal comparison in JOIN

The less than operator in the ON clause of this SELECT command will make a large joined table. The names in tblLeft that occur early alphabetically, will especially have many matches in the tblRight.

SELECT L.Name
FROM tblLeft AS L
INNER JOIN tblRight AS R ON (L.Name < R.Name)

JOIN 3 tables

It is common to join three tables to form a many-to-many relationship. In this example an author can have many titles, and a title can have many authors. 

SELECT a.au_name, t.title
FROM titleauthors AS ta
JOIN authors AS a ON (ta.au_id=a.au_id)
JOIN titles AS t ON (ta.title_id=t.title_id)
WHERE t.type = 'business'
ORDER BY a.au_name

Query optimizing

Two different queries can give the same results but one may run faster than the other. There are many factors when it comes to optimizing queries, but a large factor is the order that joins are put in. In general join SMALL to BIG to BIG, instead of BIG to BIG to SMALL.

select * from 
com.mprPatient P
join com.mprPatient_RegistryStatus_ref PS on (P.PatientStatusID=Ps.ID)
join com.mprPatient_Registry PR on (PR.AnalysisSystem=P.AnalysisSystem) and (PR.AnalysisPatientID=P.AnalysisPatientID)
--25 sec

select * from 
com.mprPatient_RegistryStatus_ref Ps
join com.mprPatient P on (P.PatientStatusID=Ps.ID)
join com.mprPatient_Registry PR on (PR.AnalysisSystem=P.AnalysisSystem) and (PR.AnalysisPatientID=P.AnalysisPatientID)
--4 sec

Page Modified: (Hand noted: 2007-10-12 20:01:38Z) (Auto noted: 2008-05-29 20:11:03Z)