Other Types of Joins – Complete Intro to SQLite

We looked at several ways of doing joins in the last section by using the JOIN keyword. One key thing about that is we were doing INNER JOINs. If you don't specify what type of join to do, it is implicitly an INNER JOIN. In practice it didn't matter because our queries matched up 1:1:

SELECT
    B.Name, A.Title
FROM
    Album A
INNER JOIN
    Artist B ON
        A.ArtistId = B.ArtistId
LIMIT
    5;

INNER JOIN or just JOIN are the same thing.

Our INNER JOIN is giving us the inner section on the Venn Diagram between the two tables. Let's say that we have an artist in the artist table that we don't have any albums for. If we just

SELECT
    B.Name, A.Title
FROM
    Album A
INNER JOIN
    Artist B ON
        A.ArtistId = B.ArtistId
WHERE
    B.Name = 'Snow Patrol';

SELECT
    B.Name, A.Title
FROM
    Album A
RIGHT JOIN
    Artist B ON
        A.ArtistId = B.ArtistId
WHERE
    B.Name = 'Snow Patrol';

Notice the first query doesn't give us any results. That's because it's an inner join – it will only give us things from rows in both tables. Now if we run the second query, it'll give anything that's in the inner part (which is nothing, as we just saw) and it will give us anything that just exists in the Artist Table (the "right" table in this case.)

LEFT refers to the FROM clause table, RIGHT refers to what comes from the JOIN.

What if we wanted to see all artists without an album in the album table?

SELECT
    B.Name, A.Title
FROM
    Album A
RIGHT JOIN
    Artist B ON
        A.ArtistId = B.ArtistId
WHERE A.ArtistId IS NULL;

The WHERE A.ArtistId IS NULL makes this an RIGHT OUTER JOIN. So only take things that don't have anything in the Album table, so it will only give us artists with no albums in the albums table.

NATURAL JOIN

SELECT
    B.Name, A.Title
FROM
    Album A
NATURAL JOIN
    Artist B
LIMIT
    5;

SELECT
    B.Name, A.Title, C.Name
FROM
    Album A
NATURAL JOIN
    Artist B
NATURAL JOIN
    Track C
LIMIT
    5;

I don't really like NATURAL JOIN but I thought I'd mention it because you'll see it from time to time. By saying NATURAL JOIN in this case, we're saying "Hey, I have columns in both tables that are named the same thing. Using that, join these tables."

It might seem convenient (and if you structure your tables well like Chinook is, it's easy) but it's so implicit. I don't like magic code and this feels magic and brittle. If you rename a column or drop it or anything like that, you can break your queries or, even worse, get the wrong data back. I'd say steer clear.

← Previous Next →