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:

  • [INNER]

    An inner join, aka an equi-join (when the SelectList is a *) or a natural join (when the SelectList is not a *). If the JOINType is not provided, then the join is INNER JOIN by default. An inner join makes the FROM clause return a joined table (with rows from both tables) only when rows are matched based on the JoinSearchCondition. For inner joins, it doesn't matter which table is left or right of the JOIN keyword.
  • {LEFT | RIGHT | FULL} [OUTER]

    An outer join functions just like inner join but also returns rows that were not matched.
    • A left outer join returns matched rows from both tables, as well as all unmatched rows from the TableLeft. The TableRight will return NULL values in its columns for the unmatched rows. For left outer joins, it does matter which table is left or right of the JOIN keyword.
    • A right outer join returns matched rows from both tables, as well as all unmatched rows from the TableRight. The TableLeft will return NULL values in its columns for the unmatched rows. For right outer joins, it does matter which table is left or right of the JOIN keyword.
    • A full outer join returns matched rows as well as unmatched rows from both tables. For rows where one table is matched but the other is not, the other table returns NULL values for its columns. For full outer joins, it doesn't matter which table is left or right of the JOIN keyword.
  • CROSS

    A cross join, aka Cartesian product. All x rows in TableLeft are matched with all y rows in TableRight. This forms a join table with x * y rows. If either table has a significant number of rows, the resulting cross join table can be massive. This is the only case where a join does not require the ON clause.

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:

  • Columns being compared do not have to have the same name.
  • Columns being compared do not have to have the same data type but must at least be compatible or implicitly converted by the DBMS. Otherwise a data type conversion function must be applied.
  • It is most common to compare key fields with other key fields.
  • The JoinSearchCondition is similar to the RowSearchCondition used in the WHERE clause. However the FROM clause should be used to state which tables are used and how they are related, whereas the WHERE clause should be used to filter out rows from the resulting tables.
  • It is possible to use operators other than equal but it often produces large joined tables.

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.

See "A Visual Explanation of SQL Joins" [codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html]

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


GeorgeHernandez.comSome rights reserved