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

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)

Example 2 ( USING syntax)

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

Example 3 (Inner Join on Three Tables)

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)

Example 2 (Films that are not in the inventory)

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

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

Include the top manager in the result set

Example 2 (Comparing the rows with the same table)

Find all pair of films that have same 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

Example 2 (Department that does not have any employees)

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

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)

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

Example 2 (Unexpected Result)

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

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