# 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`
\*
```
```
\*
```
```
* 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;`
*
```
```
* Left Join will be
* `LEFT JOIN basket_b ON fruit_a = fruit_b;`
*
```
```
* Right Join will be
* `RIGHT JOIN basket_b ON fruit_a = fruit_b;`
*
```
```
* Full Outer Join
* `FULL OUTER JOIN basket_b ON fruit_a = fruit_b;`
*
```
```
#### Venn Diagram
### 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.*****)**
*
```
```
* 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)
```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;
```
#### 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)
```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.*****)**
*
```
```
* 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)
```sql
SELECT
film.film_id,
title,
inventory_id
FROM
film
LEFT JOIN inventory
ON inventory.film_id = film.film_id
ORDER BY title;
```
#### 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;
```
### Right Join
**(*****select rows from one table that may or may not have the corresponding rows in other tables.*****)**
*
```
```
* 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
```sql
SELECT
review,
title
FROM
films
RIGHT JOIN USING (film_id);
```
### 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:
**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;
```
**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;
```
#### Example 2 (Comparing the rows with the same table)
**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;
```
### Full Outer Join
**(*****use the full join to find a row in a table that does not have a matching row in another table.*****)**
*
```
```
* 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
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;
```
#### 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;
```
### 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
### 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;
```
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)
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
***
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://notes.nomanaziz.me/development/database/sql/basics/3.-joining-multiple-tables.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
