> 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="/files/7xJeYR3Fr78tD1960mpN" 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="/files/vZeQpIEFvaAsJS4I4kWC" 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="/files/0qR0yyQSW1K41qfGQQie" 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="/files/xOYi62XSAA9RAHWhJUeP" 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="/files/HQAOEEj1MvEoGVHrc6ko" 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="/files/L9F4KL2Ign4vHqJ2B7wi" 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="/files/8lUcO8rD6wN1KUX4cCpp" 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="/files/y2Kjnk34YPYTtuBCFaFf" alt=""><figcaption></figcaption></figure>

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

<figure><img src="/files/TclUr9YEFYBPng9zPAeM" 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="/files/vXLmQBQuDCHm0HL2gsUo" 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="/files/kpJWYesa9aYxaI2XM4fy" 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="/files/dNhVfLNZkBIJdz49nFzg" alt=""><figcaption></figcaption></figure>

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

<figure><img src="/files/VSLTbnlpriXyfPn2XtqL" 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="/files/MIxMUakMy19Z9gphD8Vx" 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="/files/WrmPQVXck49aRpZsM9b3" 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="/files/ddszEZLAnPBNt3VMtW7C" 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="/files/H1kcZzVyycRyFgrn3DU3" 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="/files/MF9uhdfiZuNLuLMmtK4G" 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="/files/Hm0Z8pdwtvzaICfiVGap" 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="/files/PQYT109h5Kw0MMgYuqxR" 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

***