Suppose we have the following tables
sqlite> .headers ON sqlite> SELECT * FROM Artists; ArtistID|ArtistName 1 |Peter Gabriel 2 |Bruce Hornsby 3 |Lyle Lovett 4 |Beach Boys 5 |Supernatural sqlite> SELECT * FROM CDs; CDID|ArtistID|Title |Date 1 |1 |So |1984 2 |1 |Us |1992 3 |2 |The Way It Is |1986 4 |2 |Scenes from the Southside|1990 5 |1 |Security |1990 6 |3 |Joshua Judges Ruth |1992 7 |4 |Pet Sounds |1966
It is also called the "direct join" or Cartesian product:
sqlite>SELECT * FROM Artists, CDs;
In some SQL dialect it can be also used
sqlite>SELECT * FROM Artists CROSS JOIN CDs;
Let us try to filter the result with the WHERE clause:
sqlite>SELECT ArtistName, Title FROM Artists, CDs WHERE Artists.ArtistID=CDs.ArtistID;
Peter Gabriel|So Peter Gabriel|Us Peter Gabriel|Security Bruce Hornsby|The Way It Is Bruce Hornsby|Scenes from the Southside Lyle Lovett |Joshua Judges Ruth Beach Boys |Pet Sounds
To avoid confusion, use this statement instead
sqlite>SELECT Artists.ArtistName, CDs.Title FROM Artists, CDs WHERE Artists.ArtistID=CDs.ArtistID;
or with aliases
sqlite>SELECT a.ArtistName, c.Title FROM Artists a, CDs c WHERE a.ArtistID=c.ArtistID;
The same result can be achieved with the INNER JOIN clause:
sqlite> SELECT Artists.ArtistName, CDs.Title FROM Artists INNER JOIN CDs ON Artists.ArtistID=CDs.ArtistID;
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Artists that do not have matches in CDs, those rows will not be listed.
The LEFT OUTER JOIN operator ensures that all rows on the "left" side of the join, in this case the Artists table, will be included.
sqlite>SELECT * FROM Artists LEFT OUTER JOIN CDs ON Artists.ArtistID = CDs.ArtistID;
There is no Supernatural in CDs but you can still see Supernatural with empty fields from Artists:
ArtistID|ArtistName |CDID|ArtistID|Title |Date 1 |Peter Gabriel|1 |1 |So |1984 1 |Peter Gabriel|2 |1 |Us |1992 1 |Peter Gabriel|5 |1 |Security |1990 2 |Bruce Hornsby|3 |2 |The Way It Is |1986 2 |Bruce Hornsby|4 |2 |Scenes from the Southside|1990 3 |Lyle Lovett |6 |3 |Joshua Judges Ruth |1992 4 |Beach Boys |7 |4 |Pet Sounds |1966 5 |Supernatural | | | |
Some SQL dialects use LEFT JOIN.
In other dialects (Oracle) instead of
sqlite> SELECT Artists.ArtistName, CDs.Title ------> FROM Artists ------> LEFT OUTER JOIN CDs ------> ON Artists.ArtistID = CDs.ArtistID;
the following statement is used
SELECT Artists.ArtistName, CDs.Title FROM Artists, CDs WHERE Artists.ArtistID = CDs.ArtistID(+)
The (+) symbol denotes the table (side) that may have no matching rows to the other table (side). Think of this as everything from the left plus matching values from the right.
Similarly this SQL
SELECT Artists.ArtistName, CDs.Title FROM Artists RIGHT OUTER JOIN CDs ON Artists.ArtistID = CDs.ArtistID;
SELECT Artists.ArtistName, CDs.Title FROM Artists, CDs WHERE Artists.ArtistID(+) = CDs.ArtistID
would return all records from the right side.
Currently SQLite supports neither of the above syntax. However there is easy workaround: use LEFT OUTER JOIN with interchanged tables.
The FULL OUTER JOIN clause is not supported as well.
© Nikolai Shokhirev, 2001 - 2017