Starting SQL: Intro to JOINs

3 min readApr 13, 2022

Relational databases are great for storing data that have strong dependencies upon data in other tables. Some of the most useful SQL clauses you will use fall within the JOIN family!

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

Resources

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);

What is a JOIN?

JOIN clauses in SQL serve to combine data from two or more tables based on a common column between them like a foreign key table. We will be looking at INNER JOINs, LEFT JOINs, RIGHT JOINs and FULL OUTER JOINs.

Before we do that, let’s first familiarize ourselves with the syntax:

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

We can interpret this as:

FROM the blogs table (grab all the columns)
JOIN with authors table ON (where the following condition is true) authors.id equal to the foreign key author_id from the blogs table.
SELECT only the columns username and url because that’s what we specified

If this feels a bit clunky, its ok, we will do a couple examples.

INNER JOIN

**NOTE:** The `INNER` keyword is optional at least in databases like PostgresQL

This is the first join table we will look at and probably what you would intuitively think about when we want to produce a list of data where it must exist in both tables. Let’s look at the syntax!

If you ran the above resource code above, then you should have the following data in our database:

This is what happens when we do an INNER JOIN, again let’s go through it in the way I described in the previous section

FROM the blogs table (grab all the columns)

JOIN with authors table ON (where the following condition is true) authors.id equal to the foreign key author_id from the blogs table.

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

Note that we removed some of the columns as well as dropped any records that did not match an existing record on the joining table. This is our final result. Try it out by running the query in your database management system.

Try it!

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

Conclusion

Run this query and also try visualizing it in a spreadsheet if it helps. Look forward to the next article where we will talk about FULL OUTER JOINs!