> For the complete documentation index, see [llms.txt](https://notes.nomanaziz.me/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://notes.nomanaziz.me/development/database/sql/basics/3.-joining-multiple-tables.md).

# 3. Joining Multiple Tables

### Joins

**(*****show you a brief overview of joins in PostgreSQL.*****)**

* Used to combine columns from one or more tables based on values of common columns between related tables
* The common columns are typically **primary key** column of first table and **foreign key** column of second table

#### Examples

* Suppose we have following tables `basket_a` and `basket_b`
  \*

  ```
  <figure><img src="/files/vCtFOLZiW7ufcYEhaiMm" alt=""><figcaption></figcaption></figure>
  ```

  \*

  ```
  <figure><img src="/files/U6ZTRghwk61CUSCPQioj" alt=""><figcaption></figcaption></figure>
  ```
* The base sql statement is
  * `SELECT a, fruit_a, b, fruit_b FROM basket_a`
* Inner Join will be

  * `INNER JOIN basket_b ON fruit_a = fruit_b;`
  *

  ```
  <figure><img src="/files/Ir2Y3LUd6RyggNc5fWWN" alt=""><figcaption></figcaption></figure>
  ```
* Left Join will be

  * `LEFT JOIN basket_b ON fruit_a = fruit_b;`
  *

  ```
  <figure><img src="/files/LOdxjQG3MORENFx03HbF" alt=""><figcaption></figcaption></figure>
  ```
* Right Join will be

  * `RIGHT JOIN basket_b ON fruit_a = fruit_b;`
  *

  ```
  <figure><img src="/files/Tmhbun4Z6xLhecrFv6aL" alt=""><figcaption></figcaption></figure>
  ```
* Full Outer Join

  * `FULL OUTER JOIN basket_b ON fruit_a = fruit_b;`
  *

  ```
  <figure><img src="/files/RGgv2Qvq3eryhmIXAy6B" alt=""><figcaption></figcaption></figure>
  ```

#### Venn Diagram

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FMEuWNWGXz0pbqTTPnn4K%2Fimage.png?alt=media&amp;token=80f53fdf-4ffa-4e66-bf4b-47fbbac1504b" alt=""><figcaption></figcaption></figure>

### Table aliases

**(*****describes how to use table aliases in the query.*****)**

* Table aliases temporarily assign tables new names during the execution of a query.
* Similar to column aliases, the `AS` keyword is optional.

#### Practical applications of table aliases

1. Using table aliases for the long table name to make queries more readable
   * you can assign the table `a_very_long_table_name` an alias like this:
     * `a_very_long_table_name AS alias`
   * And reference the column\_name in the table a\_very\_long\_table\_name using the table alias:
     * `alias.column_name`
2. Using table aliases in join clauses
   * If you use the same column name that comes from multiple tables without fully qualifying them, you will get an error.
   * To avoid this error, you need to qualify these columns using the following syntax:
     * `table_name.column_name`
   * To make query shorter, we can use table aliases like this
     * ```sql
       SELECT
         c.customer_id,
         first_name,
         amount
       FROM
       	customer c
       INNER JOIN payment p
       	ON p.customer_id = c.customer_id
       ORDER BY
       	payment_date DESC;
       ```
3. Using table aliases in self-join

   * referencing the same table multiple times within a query results in an error.
   * The following example shows how to reference the `employee` table twice in the same query using the table aliases:
     * ```sql
       SELECT
         e.first_name employee,
         m.first_name manager
       FROM
       	employee e
       INNER JOIN employee m
       	ON m.employee_id = e.manager_id
       ORDER BY manager;
       ```

   ```
   ```

***

### Inner Join

**(*****select rows from one table that has the corresponding rows in other tables.*****)**

*

```
<figure><img src="/files/gzf7WgUcXaoNiJ5yp54i" alt=""><figcaption></figcaption></figure>
```

* Suppose that you have two tables A and B. The table A has a column **pka** whose value matches with values in the **fka** column of table B.

#### Example 1 (Inner Join on Two Tables)

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FhSWMytrxvuFlnyxxh0rU%2Fimage.png?alt=media&amp;token=60919923-0ab9-48ce-9255-41dd7df7eee5" alt=""><figcaption></figcaption></figure>

```sql
SELECT
	c.customer_id,
	first_name,
	last_name,
	payment_date
FROM
	customer c
INNER JOIN payment p
	ON p.customer_id = c.customer_id
ORDER BY payment_date;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2Fu5TkINjHU69ZZqQ3XcSG%2Fimage.png?alt=media&amp;token=f840386a-8624-42bd-9794-aa78487de175" alt=""><figcaption></figcaption></figure>

#### Example 2 ( `USING` syntax)

Since both tables have the same `customer_id` column, you can use the **USING** syntax:

```sql
SELECT
	customer_id,
	first_name,
	last_name,
	payment_date
FROM
	customer
INNER JOIN payment USING(customer_id)
ORDER BY payment_date;
```

#### Example 3 (Inner Join on Three Tables)

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FKGJS8pctqqLS9HoU3Mvn%2Fimage.png?alt=media&amp;token=11f36e99-ee79-4ac4-8c4d-ab98389edb07" alt=""><figcaption></figcaption></figure>

```sql
SELECT
	c.customer_id,
	c.first_name customer_first_name,
	c.last_name customer_last_name,
	s.first_name staff_first_name,
	s.last_name staff_last_name,
	amount,
	payment_date
FROM
	customer c
INNER JOIN payment p
	ON p.customer_id = c.customer_id
INNER JOIN staff s
	ON p.staff_id = s.staff_id;
ORDER BY payment_date;
```

***

### Left Join

**(*****select rows from one table that may or may not have the corresponding rows in other tables.*****)**

*

```
<figure><img src="/files/Q8Wy5bTo9Mm8fv3rDDe3" alt=""><figcaption></figcaption></figure>
```

* To join the table A with the table B table using a left join, you follow these steps:
  1. Specify columns in both tables from which you want to select data in `SELECT` clause
  2. Specify the left table `A` in `FROM` clause
  3. Specify the right table `B` in `LEFT JOIN` clause and join condition after the `ON` keyword
* Note that the `LEFT JOIN` is also referred to as `LEFT OUTER JOIN`

#### Example 1 (Films that are in the inventory)

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FZLlHnhEo7OjwoPuUDk0S%2Fimage.png?alt=media&amp;token=5c538684-b98a-4656-917c-42406cbdc043" alt=""><figcaption></figcaption></figure>

```sql
SELECT
	film.film_id,
	title,
	inventory_id
FROM
	film
LEFT JOIN inventory
	ON inventory.film_id = film.film_id
ORDER BY title;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FnAyCwrGgg5PX4ajpChxv%2Fimage.png?alt=media&amp;token=27c304eb-c173-4588-a591-da9eabb1bdd4" alt=""><figcaption></figcaption></figure>

#### Example 2 (Films that are not in the inventory)

```sql
SELECT
	f.film_id,
	title,
	inventory_id
FROM
	film f
LEFT JOIN inventory i USING (film_id)
WHERE i.film_id IS NULL
ORDER BY title;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FzCKhG1cM0vxXDY1GONM3%2Fimage.png?alt=media&amp;token=621c71a2-4688-4737-8a01-f194354c361d" alt=""><figcaption></figcaption></figure>

### Right Join

**(*****select rows from one table that may or may not have the corresponding rows in other tables.*****)**

*

```
<figure><img src="/files/u4hSxyEz20V9t5RjL6bz" alt=""><figcaption></figcaption></figure>
```

* Selects all rows from the right table whether or not they have matching rows from the left table.
* `RIGHT OUTER JOIN` is the same as `RIGHT JOIN`. The `OUTER` keyword is optional

#### Example 1

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FJn06BOsMhEKqqFYq9frV%2Fimage.png?alt=media&amp;token=141b3624-e66c-473f-af69-5d9f3b7549ed" alt=""><figcaption></figcaption></figure>

```sql
SELECT
	review,
	title
FROM
	films
RIGHT JOIN USING (film_id);
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FeXdSOQKaAla1xN8a1ibx%2Fimage.png?alt=media&amp;token=fafe601c-cf1e-479e-9d22-a570852f383f" alt=""><figcaption></figcaption></figure>

### Self-join

**(*****join a table to itself by comparing a table to itself.*****)**

* We typically use a self-join to query heirarhical data or to compare rows within the same table
* We specify the table twice with table aliases and provide join predicate after `ON` keyword
* Also, you can use the **LEFT JOIN** or **RIGHT JOIN** clause to join table to itself

#### Example 1 (Hierarchical data)

Suppose, you have the following organizational structure:

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FLWBrpRaaAtQnYv5YQul6%2Fimage.png?alt=media&amp;token=3cc31a84-68a3-47dc-a5e8-c06b3ffb6a7a" alt=""><figcaption></figcaption></figure>

**Find who reports to whom**

```sql
SELECT
	e.first_name || ' ' || e.last_name employee,
	m.first_name || ' ' || m.last_name manager
FROM
	employee e
INNER JOIN employee e ON m.employee_id = e.manager_id
ORDER BY manager;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2F3wqn8Wv3p54acwfGt0NO%2Fimage.png?alt=media&amp;token=af9f21a9-6825-4481-a28f-9bea7c1973d2" alt=""><figcaption></figcaption></figure>

**Include the top manager in the result set**

```sql
SELECT
	e.first_name || ' ' || e.last_name employee,
	m.first_name || ' ' || m.last_name manager
FROM
	employee e
LEFT JOIN employee m
	ON m.employee_id = e.manager_id
ORDER BY manager;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FgDThMWWRfQWiu6PkJ0GV%2Fimage.png?alt=media&amp;token=e1ba5865-a4fe-4de2-ad98-42fc0cc12986" alt=""><figcaption></figcaption></figure>

#### Example 2 (Comparing the rows with the same table)

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FbVVvWAXS9BDFVr6WQXrZ%2Fimage.png?alt=media&amp;token=c780ec86-2faa-495f-9983-e9392b623a95" alt=""><figcaption></figcaption></figure>

**Find all pair of films that have same length**

```sql
SELECT
	f1.title,
	f2.title,
	f1.length
FROM
	film f1
INNER JOIN film f2
	ON f1.film_id <> f2.film_id
	AND
	f1.length = f2.length;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2Fn7sGptxdtyTc93bMGIin%2Fimage.png?alt=media&amp;token=0ccbd72f-a56a-4a8f-ac0e-b6d47bc0de3c" alt=""><figcaption></figcaption></figure>

### Full Outer Join

**(*****use the full join to find a row in a table that does not have a matching row in another table.*****)**

*

```
<figure><img src="/files/i7CJOPahFU7jLUReLuap" alt=""><figcaption></figcaption></figure>
```

* The full outer join combines the results of both the left join and the right join.
* If the rows in the joined table do not match, the full outer join sets NULL values for every column of the table that does not have the matching row.

#### Example 1

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FI5Usm4tYPmwreqFqy3bD%2Fimage.png?alt=media&amp;token=b4b37e81-123b-42fc-9e72-1a6da733fec6" alt=""><figcaption></figcaption></figure>

1. Every employee who belongs to a department and every department which have an employee
2. Every employee who does not belong to a department and every department that does not have an employee

```sql
SELECT
	employee_name,
	department_name
FROM
	employees e
FULL OUTER JOIN departments d
	ON d.department_id = e.department_id;
```

#### Example 2 (Department that does not have any employees)

```sql
SELECT
	employee_name,
	department_name
FROM
	employees
FULL OUTER JOIN departments
	USING (department_id)
WHERE
	employee_name IS NULL;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FxnsdTW5X97EvF6gJkA6Q%2Fimage.png?alt=media&amp;token=dec0cc76-b15f-4ac0-baaf-9cad62504a93" alt=""><figcaption></figcaption></figure>

#### Example 3 (Find an employee who does not belong to any department)

```sql
SELECT
	employee_name,
	department_name
FROM
	employees
FULL OUTER JOIN departments
	USING (department_id)
WHERE
	department_name IS NULL;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FzLy65CVDasIWsjcSurwc%2Fimage.png?alt=media&amp;token=fb5cdd30-e201-4294-ba93-eda21ab60d4d" alt=""><figcaption></figcaption></figure>

### Cross Join

**(*****produce a Cartesian product of the rows in two or more tables.*****)**

* If T1 has n rows and T2 has m rows, the result set will have nxm rows.
* It does not have a join predicate like other join clauses

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2Fci0cYAClPCJ3MCtAYnZq%2Fimage.png?alt=media&amp;token=fc0e1187-782a-4116-a6f1-14a5d1c55171" alt=""><figcaption></figcaption></figure>

### Natural Join

**(*****join two or more tables using implicit join conditions based on the common column names in the joined tables.*****)**

* It is a join that creates an **implicit join based on the same column names in the joined tables**
* A natural join can be an inner join, left join, or right join
* If you do not specify a join explicitly, PostgreSQL will use the `INNER JOIN` by default.
* If you use the asterisk (\*) in the select list, the result will contain the following columns:
  * All the common columns, which are the columns from both tables that have the same name.
  * Every column from both tables, which is not a common column.
* You should avoid using the NATURAL JOIN whenever possible because sometimes it may cause an unexpected result
* The following shows the syntax of the PostgreSQL natural join: `FROM T1 NATURAL [INNER, LEFT, RIGHT] JOIN T2;`

#### Example 1 (Products & Categories Table)

```sql
SELECT * FROM products
NATURAL JOIN categories;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FyFAeqoIFxyBFR64ovKmy%2Fimage.png?alt=media&amp;token=e7f16f80-71b8-48f0-a6a3-cf70546d40d0" alt=""><figcaption></figcaption></figure>

The above statement is equivalent to the following statement that uses the INNER JOIN clause.

```sql
SELECT * from products
INNER JOIN categories USING (category_id);
```

#### Example 2 (Unexpected Result)

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FBw8605kry1Atwe38UUlm%2Fimage.png?alt=media&amp;token=31bfb85c-4ea6-46a6-90c0-96ef8239e321" alt=""><figcaption></figcaption></figure>

Both tables have same `country_id` column so we can use `NATURAL JOIN`

```sql
SELECT *
FROM city
NATURAL JOIN country;
```

The query returns an empty return set because:

* Both tables also have another common column called last\_update, which cannot be used for the join.
* However, the NATURAL JOIN clause just uses the last\_update column

***
