SQL Joins

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

CROSS JOIN

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;    

It gives

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;    

INNER JOIN

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.

LEFT OUTER JOIN

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.

RIGHT OUTER JOIN and FULL OUTER JOIN

 Similarly this SQL 

SELECT Artists.ArtistName, CDs.Title 
FROM Artists
RIGHT OUTER JOIN CDs
ON Artists.ArtistID = CDs.ArtistID;

or

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.

References

• Getting the Right Data with SQL Joins. http://www.devx.com/dbzone/Article/17403/0/page/1 
• SQL Join. http://www.w3schools.com/sql/sql_join.asp 
• SQLite home page, http://www.sqlite.org/ 
• SQL Features That SQLite Does Not Implement, http://www.sqlite.org/omitted.html 
• A command-line program to administer SQLite databases, http://www.sqlite.org/sqlite.html 
• SQL As Understood By SQLite, http://www.sqlite.org/lang.html 
• Frequently Asked Questions, http://www.sqlite.org/faq.html 
• SQLite Tutorial by Mike Chirico mchirico@users.sourceforge.net, http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html