SQL Joins

NATURAL JOIN does not work very well and is not recommended.

Example:

SELECT * FROM albums NATURAL JOIN artists;

Joins two tables by a common column.

LEFT JOIN - finds matches in the right table based on the left table

SELECT Title, Name FROM albums LEFT JOIN artists ON albums.artistId = artists.artistId;

Joins two tables by artistId. Since it is a left join, EVERY value from the left table WILL have a match or null. If when joining by a column, as in the example above, the column names are THE SAME in both tables, then instead of ON albums.artistId = artists.artistId you can write USING(ArtistId)

INNER JOIN - works the same way but only finds matches where there is no null. That is, every value from the left table MUST have a match, or it will be discarded if there is no match.

SELECT Title, Name FROM albums INNER JOIN artists ON albums.artistId = artists.artistId;

CROSS JOIN - every value from the left table will be matched with ALL values from the right table.

For example, if there is a table with one column "products" and values A, B, C, and a table with a column "Date" and a list of dates, then A will have a full list of dates, as will B and C.

SELECT * FROM products CROSS JOIN calendars;

JOIN can be performed on 3 or more tables as well, for example:

SELECT
trackid,
tracks.name AS Track,
albums.title AS Album,
artists.name AS Artist
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
INNER JOIN artists ON artists.artistid = albums.artistid

SELECT
users.name as 'User name',
users.email as 'User email',
CONCAT(IFNULL(books.name, ''), IFNULL(pages.name, ''), IFNULL(chapters.name, ''), IFNULL(bookshelves.name, '')) as 'Resource Name',
views.viewable_type 'Resource type',
views.views as 'View count',
views.updated_at as 'Last visited'
FROM views
LEFT JOIN users on views.user_id = users.id
LEFT JOIN books on books.id = views.viewable_id and views.viewable_type = 'book'
LEFT JOIN pages on pages.id = views.viewable_id and views.viewable_type = 'page'
LEFT JOIN chapters on chapters.id = views.viewable_id and views.viewable_type = 'chapter'
LEFT JOIN bookshelves on bookshelves.id = views.viewable_id and views.viewable_type = 'bookshelf'
WHERE views.updated_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY views.updated_at DESC;