SQL Querying Multiple Tables

Internal

• Querying Multiple Tables

Overview

More than one table can be used in a FROM query clause, and when that happens, it is said that the query performs a join before filtering and returning the results.

JOIN

The mechanism of linking two or more tables is referred to as a join. For two tables, the most generic way of combining rows is to take every row from the first table and combine it with every row with the other table. This is known as the cartesian product of two relations, or a cross join. If the two tables have N and M rows respectively, the result will have N x M rows. In case of three tables, with N, M and P rows respectively, the result will contain N x M x P rows. See the Cross Join section below for an example of how that works.

In most cases, cartesian products are not very useful, they produce a lot of redundant data. We are usually interested in combining rows that are related to each other, and dropping everything else. This is done with an inner join. The inner join can be thought of as performing the cartesian product, then retaining only the rows that satisfy the join condition. Inner joins are the most commonly used type of join. This mental model is confirmed by results of an inner join without a join condition: the cartesian product. Other types of joins are the outer joins: left outer join and right outer join. Since different types of joins are available, you should get in the habit of explicitly specifying what kind of join the statements intends to do when the syntax supports an implicit behavior.

Any kind of table (permanent, temporary, virtual and derived can be used in joins.

Examples

All examples provided in this article are based on three tables (person, address and country), with the following schema:

person 

Column     | Type                                                  id | name             | company_id
-----------+---------                                           ------+------------------+-------------
id         | integer  # primary key                                1  | Alice            | 10
name       | text                                                  2  | Bob              | 20 
company_id | integer  # foreign key that references company(id)    3  | Charlie          | 30 
                                                                   4  | Dilbert          |  

company

Column     | Type                                                  id | name             | city_id
-----------+---------                                           ------+------------------+-------------
id         | integer # primary key                                10  | Moonphone        | 100
name       | text                                                 20  | Vortextime       | 200  
city_id    | integer # foreign key that references city(id)       30  | Bluestone        | 300 

city

Column     | Type                                                  id | name             
-----------+---------                                           ------+------------------
id         | integer # primary key                                100 | San Francisco
name       | text                                                 200 | New York  
                                                                  300 | Chicago

SQL to create person/company/city

Inner Join

An inner join is performed by with the following SQL92 ANSI SQL standard syntax. The first table is specified in the FROM clause. The second and every additional table that participates to the join, are introduced by a JOIN ... ON construct. The table specified as part of the FROM clause is called the driving table. However, which one of the join tables is selected as driving table, and the order in which remaining tables are specified in the subsequent JOIN ... ON clauses is not important. This makes sense if you think that an inner join performs a cartesian product and then selects rows with join conditions that are unaffected by the order in which the tables are declared. Moreover, the query optimizer may internally pick a different table order depending on the statistics gathered from the database objects. The STRAIGHT_JOIN qualifier pins the order, if desired.

SELECT ...
  FROM table_one
    JOIN table_two ON <join_condition>
    JOIN table_three ON <join_condition>
    ...

A JOIN keyword without any qualifier implies an inner join. It is recommended to redundantly specify INNER for clarity:

SELECT ...
  FROM table_one
    INNER JOIN table_two ON <join_condition>
    INNER JOIN table_three ON <join_condition>
    ...

The two forms are equivalent.

When the name of the columns that are evaluated in the join condition is identical, ON can be replaced with USING:

SELECT ...
  FROM table_one
    JOIN table_two USING somecolumn
    ...

Also see Older ANSI Inner Join Syntax below.

A complete example of two tables inner join is:

 SELECT person.name AS name, company.name AS company
 FROM person 
   JOIN company ON person.company_id = company.id;

  name   |  company
---------+------------
 Alice   | Moonphone
 Bob     | Vortextime
 Charlie | Bluestone

A complete example of three tables inner join is:

 SELECT person.name AS name, company.name AS company, city.name AS city 
 FROM person
   JOIN company ON person.company_id = company.id 
   JOIN city ON company.city_id = city.id;

  name   |  company   |     city
---------+------------+---------------
 Alice   | Moonphone  | San Francisco
 Bob     | Vortextime | New York
 Charlie | Bluestone  | Chicago

Older ANSI Inner Join Syntax

The following syntax is equivalent. It does not use the JOIN keywords, but requires specifying all tables in the FROM clause. Also, it does not introduce the join conditions with ON, but with the WHERE clause:

SELECT ...
  FROM table_one, table_two, ...
    WHERE <join_condition>

The newer SQL92 syntax is recommended because it separates the join condition and filter conditions in two different clauses, ON and WHERE, making a query easier to understand. Also, the join conditions for each pair of tables are contained in their own ON clause, making it less likely that part of a join will be mistakenly omitted. Also, SQL92 syntax is portable across servers.

Join Condition

Tables are usually inner joined using foreign keys. Foreign keys encode relationships in data. The inner join condition states that we should associate the rows from the first table with rows from other table where the foreign key column value from the first table row is equal with the primary key column value from other table row, so a join condition based on foreign key usually looks like:

 [...] ON table_one.foreign_key_from_table_two == table_two.primary_key

Any other expression that is evaluated to a boolean value can be used. The join condition will be evaluated and filter out rows from the full cartesian product.

Outer Joins

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch10.html#:-:text=Outer%20Joins

Left Outer Join

Two-Table Left Outer Join

A left outer join starts as a cartesian product between the left table and a right table that has an additional row of NULL columns appended at the end. The N x (M + 1) rows so produced are filtered by the join condition, applied to the right table columns only. If the join condition does not select any right table row, only the right table row that contains all NULL columns is kept in the cartesian product. If at least one right table row is selected by the join condition, the NULL row is dropped. This behavior ensures that every left table row is matched with right table rows, and if no matching are found, the left table row still exists in the final result, but "padded" with NULL columns.

This is a two-table left outer join for which the join condition results in no matches. The FALSE join condition ensures that:

SELECT * 
FROM person
LEFT OUTER JOIN company ON FALSE;

The result includes every left table column padded with NULLs:

 id |  name   | company_id | id | name | city_id
----+---------+------------+----+------+---------
  1 | Alice   |         10 |    |      |
  2 | Bob     |         20 |    |      |
  3 | Charlie |         30 |    |      |
(3 rows)

A join condition ensuring that each left table row is matched with the exact on right table row, following the company_id foreign key, for example, will lead to a left outer join that has exactly three rows, which is how many persons are in the person table:

SELECT * 
FROM person
LEFT OUTER JOIN company ON person.company_id = company.id;

 id |  name   | company_id | id |    name    | city_id
----+---------+------------+----+------------+---------
  1 | Alice   |         10 | 10 | Moonphone  |     100
  2 | Bob     |         20 | 20 | Vortextime |     200
  3 | Charlie |         30 | 30 | Bluestone  |     300
(3 rows)

In this case there is no row with NULL right table columns.

This is a join condition that associates each left table row with two right table rows:

SELECT * 
FROM person
LEFT OUTER JOIN company ON person.company_id <> company.id;

 id |  name   | company_id | id |    name    | city_id
----+---------+------------+----+------------+---------
  1 | Alice   |         10 | 20 | Vortextime |     200
  1 | Alice   |         10 | 30 | Bluestone  |     300
  2 | Bob     |         20 | 10 | Moonphone  |     100
  2 | Bob     |         20 | 30 | Bluestone  |     300
  3 | Charlie |         30 | 10 | Moonphone  |     100
  3 | Charlie |         30 | 20 | Vortextime |     200
(6 rows)

The extreme is matching each left table row with all right table rows, which results in a cross join:

SELECT * 
FROM person
LEFT OUTER JOIN company ON TRUE;

 id |  name   | company_id | id |    name    | city_id
----+---------+------------+----+------------+---------
  1 | Alice   |         10 | 10 | Moonphone  |     100
  1 | Alice   |         10 | 20 | Vortextime |     200
  1 | Alice   |         10 | 30 | Bluestone  |     300
  2 | Bob     |         20 | 10 | Moonphone  |     100
  2 | Bob     |         20 | 20 | Vortextime |     200
  2 | Bob     |         20 | 30 | Bluestone  |     300
  3 | Charlie |         30 | 10 | Moonphone  |     100
  3 | Charlie |         30 | 20 | Vortextime |     200
  3 | Charlie |         30 | 30 | Bluestone  |     300
(9 rows)

Three-Table Left Outer Join

Same algorithm applies, for each additional right table, where each join condition applies to the corresponding table only.

Right Outer Join

Symmetric to Left Outer Join.

Cross Join

A cross join produces the cartesian product of the tables participating in join.

For two tables:

 
SELECT * FROM person 
  CROSS JOIN company;

 id |  name   | company_id | id |    name    | city_id
----+---------+------------+----+------------+---------
  1 | Alice   |         10 | 10 | Moonphone  |     100
  1 | Alice   |         10 | 20 | Vortextime |     200
  1 | Alice   |         10 | 30 | Bluestone  |     300
  2 | Bob     |         20 | 10 | Moonphone  |     100
  2 | Bob     |         20 | 20 | Vortextime |     200
  2 | Bob     |         20 | 30 | Bluestone  |     300
  3 | Charlie |         30 | 10 | Moonphone  |     100
  3 | Charlie |         30 | 20 | Vortextime |     200
  3 | Charlie |         30 | 30 | Bluestone  |     300
  4 | Dilbert |            | 10 | Moonphone  |     100
  4 | Dilbert |            | 20 | Vortextime |     200
  4 | Dilbert |            | 30 | Bluestone  |     300
(12 rows)

This is the same as performing an inner join without a join condition.

 
SELECT * FROM person, company;

For three tables:

 
SELECT COUNT(*) FROM person 
  CROSS JOIN company
  CROSS JOIN city;

 count
-------
   36

Using Table Aliases in Joins

The SQL syntax permits to alias the tables participating in join. These table aliases can be used to identify the tables when you reference columns in the SELECT clause:

 FROM table_one AS table_one_alias
   JOIN table_two AS table_two_alias ON <join_condition>
   JOIN table_three AS table_three_alias ON <join_condition>
   ...

For permanent, temporary and virtual tables aliasing is optional, their original names are usually sufficient to reference columns. However for derived tables, the alias is required if we want to refer to column from the derived table:

SELECT person.name, derived_table_alias.name 
FROM person 
  JOIN (SELECT id, UPPER(name) AS name FROM company) AS derived_table_alias ON person.company_id = derived_table_alias.id;

Using the Same Table Twice

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch05.html#:-:text=Using%20the%20Same%20Table%20Twice

Self-Joins

Self-joins are useful when a table includes a self-referencing foreign key. TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch05.html#:-:text=Self-Joins