PostgreSQL: How To Connect Two Tables (Joins)

Intro

We learned:

• how to create a table
• how to create simple queries

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.

---

Further Reading

PostgreSQL Homepage
PostgreSQL Docs
SQL Syntax
Joins Docs
Graphical Visualization