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

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.

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 such produced are filtered by the join condition, applied to the right table columns only. If no non-NULL right table row is selected by the join condition, the NULL right table column are kept in the cartesian product. If at least one right table row is selected by the join condition, the NULL column right table 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.

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