PostgreSQL: How To Connect Two Tables (Joins)
Tags: beginners, tutorial, postgres, javascript
Published: 2019-09-11 09:12:42

---
title: PostgreSQL: How To Connect Two Tables (Joins)
published: true
description: PostgreSQL: How To Connect Two Tables (Joins)
tags: beginners, tutorial, postgres, javascript
series: Intro to PostgreSQL (for NodeJS & Express)
---

## Intro

We learned:
- [how to create a table ](https://dev.to/miku86/postgresql-how-to-create-our-first-table-1j82)
- [how to create simple queries](https://dev.to/miku86/postgresql-how-to-create-some-basic-queries-57l8)

Now we want to learn how to connect two tables.

---

## Connect to the Database Shell

First, I connect to my database's shell:

```sql
psql -d miku86-db
```

---

## Create Two Tables

### Table 1: `breed`

- Create table:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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](http://www.postgresqltutorial.com/postgresql-joins/) is a great graphical visualization of the various Joins.

---

## Further Reading
[PostgreSQL Homepage](https://www.postgresql.org/)
[PostgreSQL Docs](https://www.postgresql.org/docs/current/index.html)
[SQL Syntax](https://en.wikipedia.org/wiki/SQL_syntax)
[Joins Docs](https://www.postgresql.org/docs/current/tutorial-join.html)
[Graphical Visualization](http://www.postgresqltutorial.com/postgresql-joins/)