Artful Common Queries

JOIN intro

from the Artful Common Queries page

A database table stores a set of rows. A JOIN combines two or more such sets. It may do so in different ways. Draw two circles on a piece of paper, Make sure they overlap to some degree. Put an A in the non-overlapped part of left area, put a B in the overlapped area, put a C in the non-overlapped part of the right area. Using ASCII art it would look like this:

                +-----+   +-----+
               /       \ /       \
              /         x         \
             /         / \         \
            |    A    | B |    C    | 
             \         \ /         /
              \         x         /
               \       / \       /
                +-----+   +-----+

Let A be authors and C be books:

CREATE TABLE auteurs(auteurID int, nom char(32), prenom char(16));
CREATE TABLE livres(livreID int,auteurID int,titre char(64));

Then ...

A is the set of authors with no books
B is the set of authors with books (or books with authors)
C is the set of books with no authors
A+B is the set of authors with and without books
C+B is the set of books with and without authors

A CROSS JOIN returns AxC, every logically possible combination of rows from A and rows from C. So to list every possible author-title combination, write:

SELECT a.nom,c.titre
FROM a JOIN c;

An INNER JOIN returns B, the rows from A and rows from C which match on some comparison(s). You may further restrict set B with a WHERE clause. To retrieve set B matching A and C on auteurID, write ...

SELECT a.nom, a.prenom, l.titre
FROM auteurs a
INNER JOIN titreslibres l ON l.titreID = a.auteurID;

The word 'INNER' is optional. The list of all auteurs and their books, including auteurs who have not published a book, is the set AB, delivered by a LEFT [OUTER] JOIN, which we write by changing one word in the above query ...

SELECT a.nom, a.prenom, l.titre
FROM auteurs a
LEFT OUTER JOIN livres l ON l.titreID = a.auteurID;

'OUTER' is optional. 'LEFT' in LEFT JOIN means the entire table whose name is to the left of the predicate is to be returned. The auteurs table is to the left of the predicate, so all auteurs rows will be part of the result even if they have no matching livres rows. Symmetry prevails. What can be done with the left table can also be done with the right: the set of all livres rows with and without matching auteurs rows can be got by changing LEFT to RIGHT in the above query:

SELECT a.nom, a.prenom, l.titre
FROM auteurs a
RIGHT JOIN livres l ON l.titreID = a.auteurID;

And yes, we get the same result be swapping table names instead of 'RIGHT' and 'LEFT':

SELECT a.nom, a.prenom, l.titre
FROM livres l
LEFT JOIN auteurs a ON l.titreID = a.auteurID;

To retrieve all rows in one table with no matching row in the other, for example auteurs without livres, write the LEFT JOIN and add an exclusion condition in a WHERE clause:

SELECT a.nom, a.prenom, l.titre
FROM auteurs a
LEFT OUTER JOIN livres l ON l.titreID = a.auteurID;
WHERE l.id IS NULL;

OUTER JOINs return NULL for every column in the "other" table if the ON condition is not met. In this case, you would only see values for the columns that come from livres if the tables actually matched up. Wherever they don't, you will see all NULLs.

Last updated 16 Aug 2024