DEV Community

miku86
miku86

Posted on

PostgreSQL: How To Connect Two Tables (Joins)

#beginners #tutorial #postgres #javascript

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • Check table: 
miku86-db=# SELECT * FROM breed; breed_id | breed_name ---------------+------------ 1 | Labrador 2 | Poodle (2 rows)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • Check table: 
miku86-db=# SELECT * FROM dog; dog_id | dog_name | breed_id -------------+----------+---------- 1 | Rex | 1 2 | Anny | (2 rows)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Graphical Visualization

Here is a great graphical visualization of the various Joins.

Further Reading

PostgreSQL Homepage
PostgreSQL Docs
SQL Syntax
Joins Docs
Graphical Visualization

Top comments (0)