PostgreSQL: How To Connect Two Tables (Joins)

2019-09-11

Intro

We learned:

Now we want to learn how to connect two tables.

Connect to the Database Shell

First, I connect to my database's shell:

psql -d miku86-db

Create Two Tables

Table 1: `breed`

Create table:

miku86-db=# CREATE TABLE breed (breed_id SERIAL PRIMARY KEY, breed_name TEXT NOT NULL);
CREATE TABLE

Every breed must have a breed_id (unique) and a breed_name.

Add data into table:

miku86-db=# INSERT INTO breed (breed_name) VALUES ('Labrador');
INSERT 0 1
miku86-db=# INSERT INTO breed (breed_name) VALUES ('Poodle');
INSERT 0 1

Check table:

miku86-db=# SELECT * FROM breed;
 breed_id | breed_name
----------+------------
        1 | Labrador
        2 | Poodle
(2 rows)

We created two breeds, both have a breed_id and a breed_name.

Table 2: `dog`

Create table:

miku86-db=# CREATE TABLE dog (dog_id SERIAL PRIMARY KEY, dog_name TEXT NOT NULL, breed_id INT REFERENCES breed(breed_id));
CREATE TABLE

Every dog must have a dog_id (unique) and a dog_name, and can have a breed_id.

Add data into table:

miku86-db=# INSERT INTO dog (dog_name, breed_id) VALUES ('Rex', 1);
INSERT 0 1
miku86-db=# INSERT INTO dog (dog_name) VALUES ('Anny');
INSERT 0 1

Check table:

miku86-db=# SELECT * FROM dog;
 dog_id | dog_name | breed_id
--------+----------+----------
      1 | Rex      |        1
      2 | Anny     |
(2 rows)

We created two dogs, both have a dog_id and a dog_name, but only Rex has a breed_id, Anny doesn't (perhaps we couldn't figure it out).

Connect The Tables

Every row in the left table that matchs with a row in the right table: Inner Join

miku86-db=# SELECT * FROM dog JOIN breed ON dog.breed_id = breed.breed_id;
 dog_id | dog_name | breed_id | breed_id | breed_name
--------+----------+----------+----------+------------
      1 | Rex      |        1 |        1 | Labrador
(1 row)

Rex has a breed_id, that exists in the breed table, therefore he is displayed. Anny does not, therefore she is not displayed.

Every row from the left table: Left Join

miku86-db=# SELECT * FROM dog LEFT JOIN breed ON dog.breed_id = breed.breed_id;
 dog_id | dog_name | breed_id | breed_id | breed_name
--------+----------+----------+----------+------------
      1 | Rex      |        1 |        1 | Labrador
      2 | Anny     |          |          |
(2 rows)

Rex is in the left table, therefore he is displayed. Anny is in the left table, therefore she is displayed.

Every row from the right table: Right Join

miku86-db=# SELECT * FROM dog RIGHT JOIN breed ON dog.breed_id = breed.breed_id;
 dog_id | dog_name | breed_id | breed_id | breed_name
--------+----------+----------+----------+------------
      1 | Rex      |        1 |        1 | Labrador
        |          |          |        2 | Poodle
(2 rows)

Labrador is in the right table, therefore it is displayed. Poodle is in the right table, therefore it is displayed.

Every row from the left table & every row from the right table: Full Join

miku86-db=# SELECT * FROM dog FULL JOIN breed ON dog.breed_id = breed.breed_id;
 dog_id | dog_name | breed_id | breed_id | breed_name
--------+----------+----------+----------+------------
      1 | Rex      |        1 |        1 | Labrador
      2 | Anny     |          |          |
        |          |          |        2 | Poodle
(3 rows)

Graphical Visualization

Here is a great graphical visualization of the various Joins.