Starting SQL: FULL OUTER JOINs

4 min readApr 13, 2022

In the last article, we broke down what JOINs are and how to read them. This time, let’s look at another JOIN clause called the FULL OUTER JOIN. We can use FULL OUTER JOINs when we want to combine the all of the data from multiple tables that share a common field to include their overlap as well as places they don’t overlap OR we can also select only the data that doesn’t overlap

Two ways we can use **FULL OUTER JOINs**

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 from the last lesson, copy the following SQL transactions into .sqlfile 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 FULL JOINs

As before let’s first familiarize ourselves with the syntax:

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

We can interpret this as:

FROM the blogs table (grab all the columns)
FULL OUTER JOIN with authors table ON (where the following condition is true) authors.id equal to the foreign key author_id from the blogs table. Because of the FULL OUTER JOIN clause, it will also add the data that doesn’t satisfy the condition following ON.
SELECT only the columns username and url because that’s what we specified

FULL OUTER JOIN (all records)

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

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

Let’s see what happens when we do a FULL OUTER JOIN where we look to grab inclusive and exclusive data, let’s go through it together!

FROM the blogs table (grab all the columns)

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

For records that don’t have any matching records in the joining table, those cells are instead filled with NULL.

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

FULL OUTER JOIN (all exclusive records)

Starting with the same tables as before. We want only the records that are not inclusive between blogs and authors . This one is a little bit more complex but as long as we break it down the same way we did before, I’m sure it will make more sense.

SELECT url, username
FROM blogs
FULL OUTER JOIN authors ON authors.id = blogs.author_id
   WHERE
      authors.id IS NULL
   OR
      blogs.author_id IS NULL;

FROM the blogs table (grab all the columns)

FULL OUTER JOIN with authors table ON (where the following condition is true) authors.id matches to the foreign key author_id from the blogs table. BUT only keep the records WHERE blogs.author_id is a NULL value OR authors.id is a NULL value.

Yellow highlighted columns are the records we are targeting.

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

If you run this in your database management system you should get a similar result.

Conclusion

The FULL JOINs are a bit confusing and might be difficult to memorize all of the syntax. You can always look up the common syntax and you will see many similar examples. In the next article, we will look at LEFT JOINs and RIGHT JOINs!