Joins Overview

So far, we’ve been using INNER JOIN, which it’s the most common used one, but there are more. We’ll be using the old Jeff Atwood’s post about SQL joins as a base for our explanation.

Create a Test database

Let’s create a database named test_joins so we can add a couple of tables to explain a few more SQL joins. Open the terminal and run:

createdb -h localhost -p 5432 -U bob test_joins

Now let’s create a connection to this new database:

import sqlalchemy
engine = sqlalchemy.create_engine('postgresql://bob:1234@localhost:5432/test_joins')
engine.execution_options(isolation_level="AUTOCOMMIT")
%load_ext sql
%sql $engine.url

'Connected: bob@test_joins'

Create Tables

Now let’s add a couple of tables to test_joins. The first:

%%sql
CREATE TABLE "TableA" (
    id serial PRIMARY KEY,
	name VARCHAR ( 50 ) UNIQUE NOT NULL
);

 * postgresql://bob:***@localhost:5432/test_joins
Done.

[]

Let’s check that TableA has been created:

%%sql
SELECT * FROM "TableA" WHERE false;

 * postgresql://bob:***@localhost:5432/test_joins
0 rows affected.

id	name

Now let’s insert some data into TableA:

%%sql
INSERT INTO
    "TableA" (name)
VALUES
    ('Pirate'),
    ('Monkey'),
    ('Ninja'),
    ('Spaguetti');

 * postgresql://bob:***@localhost:5432/test_joins
4 rows affected.

[]

Let’s check the data has been properly inserted:

%%sql
SELECT * FROM "TableA";

 * postgresql://bob:***@localhost:5432/test_joins
4 rows affected.

id	name
1	Pirate
2	Monkey
3	Ninja
4	Spaguetti

Great, let’s do something similar for TableB:

%%sql
CREATE TABLE "TableB" (
    id serial PRIMARY KEY,
	name VARCHAR ( 50 ) UNIQUE NOT NULL
);

INSERT INTO
    "TableB" (name)
VALUES
    ('Rutabaga'),
    ('Pirate'),
    ('Darth Vader'),
    ('Ninja')
RETURNING *;

 * postgresql://bob:***@localhost:5432/test_joins
Done.
4 rows affected.

id	name
1	Rutabaga
2	Pirate
3	Darth Vader
4	Ninja

INNER JOIN

Let’s refresh our knowledge doing an INNER JOIN on the name column:

%%sql
SELECT * FROM "TableA"
INNER JOIN "TableB"
ON "TableA".name = "TableB".name;

 * postgresql://bob:***@localhost:5432/test_joins
2 rows affected.

id	name	id_1	name_1
1	Pirate	2	Pirate
3	Ninja	4	Ninja

As you can see, INNER JOIN produces only the set of records that match in both TableA and TableB, meaning those rows where the column name have the same value.

By the way, since the INNER JOIN statement is the default type of join, sometimes you’ll see it written just as JOIN.

Quite often, Venn diagrams are used to explain SQL joins. The following one is supposed to describe the inner join:

FULL OUTER JOIN

A FULL OUTER JOIN produces the set of all rows from both TableA and Table B where:

• Rows with matching values are paired on the column we’re using for the join.

• Null for those rows where the column used for the join has no match in the joined table.

Let’s see an example:

%%sql
SELECT * FROM "TableA"
FULL OUTER JOIN "TableB"
ON "TableA".name = "TableB".name;

 * postgresql://bob:***@localhost:5432/test_joins
6 rows affected.

id	name	id_1	name_1
1	Pirate	2	Pirate
2	Monkey	None	None
3	Ninja	4	Ninja
4	Spaguetti	None	None
None	None	3	Darth Vader
None	None	1	Rutabaga

Note that we’re getting None instead of null, because we’re using Alchemy to connect to the database.

For this type of join, the following diagram is used:

Excluding all rows with common values

What if we’re interested in those rows which have no corresponding values on the column we’re using to do the join? Check this out:

%%sql
SELECT * FROM "TableA"
FULL OUTER JOIN "TableB"
ON "TableA".name = "TableB".name
WHERE "TableA".id IS null
OR "TableB".id IS null;

 * postgresql://bob:***@localhost:5432/test_joins
4 rows affected.

id	name	id_1	name_1
2	Monkey	None	None
4	Spaguetti	None	None
None	None	3	Darth Vader
None	None	1	Rutabaga

As you can see, we’re excluding all rows with matching values on name. For this type of join, the following diagram is used:

LEFT OUTER JOIN

In a LEFT OUTER JOIN all rows from TableA are retrieved, and only the rows of Table B with matching values on the column we’re using for the join. All the rows of TableA which have no match will be paired with a null on the right. For example:

%%sql
SELECT * FROM "TableA"
LEFT OUTER JOIN "TableB"
ON "TableA".name = "TableB".name;

 * postgresql://bob:***@localhost:5432/test_joins
4 rows affected.

id	name	id_1	name_1
1	Pirate	2	Pirate
2	Monkey	None	None
3	Ninja	4	Ninja
4	Spaguetti	None	None

For this type of join, the following diagram is used:

Excluding all rows from the left

What if we’re interested in those records from TableA that have no corresponding record in TableB? Check this out:

%%sql
SELECT * FROM "TableA"
LEFT OUTER JOIN "TableB"
ON "TableA".name = "TableB".name
WHERE "TableB".name IS null;

 * postgresql://bob:***@localhost:5432/test_joins
2 rows affected.

id	name	id_1	name_1
2	Monkey	None	None
4	Spaguetti	None	None

As you can see, thanks to the use of the WHERE clause, we’ve achieved two things:

• Get the rows of TableA with no match in TableB.

• Exclude all results from the table to the right (in this case TableB).

For this type of join, the following diagram is used: