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.
Further Reading
PostgreSQL Homepage
PostgreSQL Docs
SQL Syntax
Joins Docs
Graphical Visualization
Top comments (0)