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

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
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
  - 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;
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:
  - 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)

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:

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)

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)

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)

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

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

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

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

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

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

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)

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)

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)

SELECT * FROM products
NATURAL JOIN categories;

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

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

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