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
`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 JOIN` s 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 `NULL` s. |