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

     • 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:

     • 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

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

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

---