Starting SQL: LEFT & RIGHT JOINs

This is the last article in this series about joins. This time we will be looking at getting inclusive data from multiple tables, and then data from either the source or the joining table

In this multi-part article you will be doing a bit of SQL query writing in this lesson so please have access to a relational database management system (i.e. PostgreSQL)!

Resources

If you haven’t already, copy the following SQL transactions into a .sql file or your terminal logged into your relational database manager and run.

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);
INSERT INTO authors (username) 
VALUES 
    ('RocketMan1969'),
    ('xXSlagathorXx'),
    ('Darkiplier'),
    ('Lemonsalmonbutter');
CREATE TABLE blogs (
    id SERIAL PRIMARY KEY,
    url VARCHAR(255),
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);
INSERT INTO blogs (url, author_id)
VALUES
    ('http://RocketMan1969.com/1', 1),
    ('http://RocketMan1969.com/425', 1),
    ('http://xXSlagathorXx.com/15', 2),
    ('http://Darkiplier.com/04', 3),
    ('http://WayneThePebbleJameson.com/360', null);

Reading LEFT JOINs

Let’s say we want to select all blogs and the username who authored the blog as well as articles with no author. That’s weird! you might say, how can a blog not have an author? For this example, we won’t concern ourselves with why but rather looking at this as an exercise. We might be looking for something that looks like this:

In the following examples, I will be referring to the table indicated by the FROM clause as the source table. It will also be the LEFT table. The JOIN clause will indicate the table we are joining in, or in other words the right table.

The code we need to run to do this operation may look like this:

SELECT url, username
FROM blogs
LEFT JOIN authors ON authors.id = blogs.author_id;

• FROM the blogs table (grab all the columns)

• LEFT JOIN with authors table ON (where the following condition is true) authors.id matches to the foreign key author_id from the blogs table. The LEFT JOIN keyword will automatically also keep any records that do not have a match.

Note: for blog at id = 5, the corresponding data from authors table is set to NULL

• SELECT only the columns url and user because that’s what we specified

RIGHT JOINs

A RIGHT JOIN would do the opposite of a LEFT JOIN. Give the code below a try, and see if you can visualize what is happening just like what we did above.

SELECT url, username
FROM blogs
RIGHT JOIN authors ON authors.id = blogs.author_id;

You should end up with a table that looks something like this:

Conclusion

Hopefully this series has helped you to better understand the different kinds JOINs that we can do on our relations to produce useful combinations of data.

If you remember from the previous article regarding FULL OUTER JOINs (exclusive data), you can copy the same WHERE clause in your LEFT and RIGHT joins to find only records that do not have a match in the source or joining table. Give it a try!

SELECT url, username
FROM blogs
LEFT JOIN authors ON authors.id = blogs.author_id
WHERE authors.id IS NULL;
SELECT url, username
FROM blogs
RIGHT JOIN authors ON authors.id = blogs.author_id
WHERE blogs.author_id IS NULL;