SQL Querying Multiple Tables: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(108 intermediate revisions by the same user not shown)
Line 4: Line 4:
* [[SQL_SELECT#Querying_Multiple_Tables|Querying Multiple Tables]]
* [[SQL_SELECT#Querying_Multiple_Tables|Querying Multiple Tables]]
=Overview=
=Overview=
More than one table can be used in a <code>[[SQL_SELECT#Querying_Multiple_Tables|FROM]]</code> query clause, and when that happens, it is said that the query performs a [[#Join|join]].
More than one table can be used in a <code>[[SQL_SELECT#Querying_Multiple_Tables|FROM]]</code> query clause, and when that happens, it is said that the query performs a [[#Join|join]] before filtering and returning the results.
 
=<span id='Join'></span><tt>JOIN</tt>=
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 <span id='Cartesian_Product'></span>'''cartesian product''' of two relations, or a [[#Cross_Join|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|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|inner join]]. The inner join can be thought of as performing the cartesian product, then retaining only the rows that satisfy the [[#Join_Condition|join condition]]. Inner joins are the most commonly used type of join. This mental model is confirmed by results of an [[#Inner_Join_without_Join_Condition|inner join without a join condition]]: the cartesian product. Other types of joins are the outer joins: [[#Left_Outer_Join|left outer join]] and [[#Right_Outer_Join|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 ([[SQL#Permanent_Table|permanent]], [[SQL#Temporary_Table|temporary]], [[SQL#Virtual|virtual]] and [[SQL#Derived_Table|derived]] can be used in joins.


=Examples=
=Examples=
Line 16: Line 23:
  name      | text                                                  2  | Bob              | 20  
  name      | text                                                  2  | Bob              | 20  
  company_id | integer  <font color=teal># foreign key that references company(id)</font>    3  | Charlie          | 30  
  company_id | integer  <font color=teal># foreign key that references company(id)</font>    3  | Charlie          | 30  
                                                                    4  | Dilbert          | 
   
   
  <b>company</b>
  <b>company</b>
Line 34: Line 42:
</font>
</font>


=Join=
{{Internal|Person_Company_City|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 <code>FROM</code> clause. The second and every additional table that participates to the join, are introduced by a <code>JOIN ... ON </code> construct. The table specified as part of the <code>FROM</code> 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 <code>JOIN ... ON</code> 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 <code>STRAIGHT_JOIN</code> qualifier pins the order, if desired.


If more than one table appears in the <code>FROM</code> clause, then the conditions used to '''link''' the tables must be included as well. This is the ANSI-approved method of joining multiple tables, and it is the most portable across various database servers. The mechanism of linking two or more tables is referred to as a '''join'''. The syntax requires that all the tables are specified in the <code>FROM</code> clause. One of the table does not need any qualifiers, then for each additional table, an:
<font size=-1.5>
<font size=-1.5>
  <font color=green><b>JOIN</b></font> <table_name> <font color=green><b>ON</b></font> <join_condition>
  <font color=green><b>SELECT</b></font> ...
  <font color=green><b>FROM</b></font> <font color=Gray>table_one</font>
    <font color=green><b>JOIN</b></font> <font color=Gray>table_two</font> <font color=green><b>ON</b></font> <join_condition>
    <font color=green><b>JOIN</b></font> <font color=Gray>table_three</font> <font color=green><b>ON</b></font> <join_condition>
    ...
</font>
</font>
is added as in the following example:
A <code>JOIN</code> keyword without any qualifier implies an inner join. It is recommended to redundantly specify <code>INNER</code> for clarity:
<font size=-1.5>
<font size=-1.5>
  <font color=green><b>FROM</b></font> <table_one>
<font color=green><b>SELECT</b></font> ...
    <font color=green><b>JOIN</b></font> <table_two> <font color=green><b>ON</b></font> <join_condition>
  <font color=green><b>FROM</b></font> <font color=Gray>table_one</font>
    <font color=green><b>JOIN</b></font> <table_three> <font color=green><b>ON</b></font> <join_condition>
    <font color=green><b>INNER JOIN</b></font> <font color=Gray>table_two</font> <font color=green><b>ON</b></font> <join_condition>
    ...
    <font color=green><b>INNER JOIN</b></font> <font color=Gray>table_three</font> <font color=green><b>ON</b></font> <join_condition>
    ...
</font>
</font>
Note that a <code>JOIN</code> keyword without any qualifier defaults to an [[#Inner_Join|inner join]].
The two forms are equivalent.  


The SQL syntax permits to alias each of the tables that participate in join, so you can identify which tables you are referring to when you reference columns in the <code>SELECT</code> clause, by using [[SQL_SELECT#Table_Aliases|table aliases]]:
When the name of the columns that are evaluated in the join condition is identical, <code>ON</code> can be replaced with <code>USING</code>:
<font size=-1.5>
<font size=-1.5>
  <font color=green><b>FROM</b></font> <table_one> <font color=green><b>AS</b></font> <font color=DarkSlateGray>table_one_alias</font>
<font color=green><b>SELECT</b></font> ...
    <font color=green><b>JOIN</b></font> <table_two> <font color=green><b>AS</b></font> <font color=DarkSlateGray>table_two_alias</font> <font color=green><b>ON</b></font> <join_condition>
  <font color=green><b>FROM</b></font> <font color=Gray>table_one</font>
    <font color=green><b>JOIN</b></font> <table_three> <font color=green><b>AS</b></font> <font color=DarkSlateGray>table_three_alias</font> <font color=green><b>ON</b></font> <join_condition>
    <font color=green><b>JOIN</b></font> <font color=Gray>table_two</font> <font color=green><b>USING</b></font> <font color=Gray>somecolumn</font>
    ...
    ...
</font>
For [[SQL#Permanent_Table|permanent]], [[SQL#Temporary_Table|temporary]] and [[SQL#Virtual|virtual]] tables aliasing is optional, as their original names can be used to reference columns. However for [[SQL#Derived_Table|derived tables]], the alias is useful:
<font size=-1.5>
  <font color=green><b>FROM</b></font> <table_one> <font color=green><b>AS</b></font> <font color=DarkSlateGray>table_one_alias</font>
    <font color=green><b>JOIN</b></font> <table_two> <font color=green><b>AS</b></font> <font color=DarkSlateGray>table_two_alias</font> <font color=green><b>ON</b></font> <join_condition>
    <font color=green><b>JOIN</b></font> <table_three> <font color=green><b>AS</b></font> <font color=DarkSlateGray>table_three_alias</font> <font color=green><b>ON</b></font> <join_condition>
    ...
</font>
</font>


To join two tables:
Also see [[#Older_ANSI_Inner_Join_Syntax|Older ANSI Inner Join Syntax]] below.
 
A complete example of two tables inner join is:


<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
  SELECT person.name AS name, company.name AS company
  SELECT person.name AS name, company.name AS company
  FROM person  
  FROM person  
   JOIN company ON person.company_id = company.id
   JOIN company ON person.company_id = company.id;
</syntaxhighlight>
</syntaxhighlight>


Line 80: Line 90:
</font>
</font>


To join three tables:
A complete example of three tables inner join is:


<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
Line 95: Line 105:
   Bob    | Vortextime | New York
   Bob    | Vortextime | New York
   Charlie | Bluestone  | Chicago
   Charlie | Bluestone  | Chicago
</font>
==Older ANSI Inner Join Syntax==
The following syntax is equivalent. It does not use the <code>JOIN</code> keywords, but requires specifying all tables in the <code>FROM</code> clause. Also, it does not introduce the [[#Join_Condition|join conditions]] with <code>ON</code>, but with the <code>WHERE</code> clause:
<font size=-1.5>
<font color=green><b>SELECT</b></font> ...
  <font color=green><b>FROM</b></font> <font color=Gray>table_one</font>, <font color=Gray>table_two</font>, ...
    <font color=green><b>WHERE</b></font> <join_condition>
</font>
The newer SQL92 syntax is recommended because it separates the [[#Join_Condition|join condition]] and [[SQL_WHERE#Filter_Conditions|filter conditions]] in two different clauses, <code>ON</code> and <code>WHERE</code>, making a query easier to understand. Also, the join conditions for each pair of tables are contained in their own <code>ON</code> 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 [[Relational_Databases#Foreign_Key|foreign keys]]. Foreign keys encode [[Relational_Databases#Relationship_Multiplicity_(Cardinality)|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:
<font size=-1.5>
  [...] <font color=green><b>ON</b></font> <font color=gray>table_one.foreign_key_from_table_two == table_two.primary_key</font>
</font>
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=
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch10.html#:-:text=Outer%20Joins</font>
=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 <code>NULL</code> columns appended at the end. The N x (M + 1) rows so produced are filtered by the [[#Join_Condition|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 <code>NULL</code> columns is kept in the cartesian product. If at least one right table row is selected by the join condition, the <code>NULL</code> 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 <code>NULL</code> columns.
This is a two-table left outer join for which the join condition results in no matches. The <code>FALSE</code> join condition ensures that:
<syntaxhighlight lang='sql'>
SELECT *
FROM person
LEFT OUTER JOIN company ON FALSE;
</syntaxhighlight>
The result includes every left table column padded with <code>NULL</code>s:
<font size=-2>
  id |  name  | company_id | id | name | city_id
----+---------+------------+----+------+---------
  1 | Alice  |        10 |    |      |
  2 | Bob    |        20 |    |      |
  3 | Charlie |        30 |    |      |
(3 rows)
</font>
A join condition ensuring that each left table row is matched with the exact on right table row, following the <code>company_id</code> foreign key, for example, will lead to a left outer join that has exactly three rows, which is how many persons are in the <code>person</code> table:
<syntaxhighlight lang='sql'>
SELECT *
FROM person
LEFT OUTER JOIN company ON person.company_id = company.id;
</syntaxhighlight>
<font size=-2>
  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)
</font>
In this case there is no row with <code>NULL</code> right table columns.
This is a join condition that associates each left table row with '''two''' right table rows:
<syntaxhighlight lang='sql'>
SELECT *
FROM person
LEFT OUTER JOIN company ON person.company_id <> company.id;
</syntaxhighlight>
<font size=-2>
  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)
</font>
</font>


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


=Join Types=
==Inner Join==
Code <code>JOIN</code> is specified without any qualifier, it implies an '''inner join'''. For clarity, <code>INNER</code> can be specified:
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
  SELECT ...
SELECT *
FROM ...
FROM person
  INNER JOIN sometable ON ...
LEFT OUTER JOIN company ON TRUE;
</syntaxhighlight>
 
<font size=-2>
  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)
</font>
 
==Three-Table Left Outer Join==
<font color=darkkhaki>Same algorithm applies, for each additional right table, where each join condition applies to the corresponding table only.</font>
 
=Right Outer Join=
<font color=darkkhaki>Symmetric to [[#Left_Outer_Join|Left Outer Join]].</font>
 
=Cross Join=
A '''cross join''' produces the [[Cartesian_Product |cartesian product]] of the tables participating in join.
 
For two tables:
 
<syntaxhighlight lang='sql'>
SELECT * FROM person
  CROSS JOIN company;
</syntaxhighlight>
<font size=-2>
  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)
</font>
 
<span id='Inner_Join_without_Join_Condition'></span>This is the same as performing an [[#Inner_Join|inner join]] without a [[#Join_Condition|join condition]].
<syntaxhighlight lang='sql'>
SELECT * FROM person, company;
</syntaxhighlight>
For three tables:
<syntaxhighlight lang='sql'>
SELECT COUNT(*) FROM person
  CROSS JOIN company
  CROSS JOIN city;
</syntaxhighlight>
<font size=-2>
  count
-------
    36
</font>
 
=Using Table Aliases in Joins=
 
The SQL syntax permits to alias the tables participating in join. These [[SQL_SELECT#Table_Aliases|table aliases]] can be used to identify the tables when you reference columns in the <code>SELECT</code> clause:
<font size=-1.5>
  <font color=green><b>FROM</b></font> <font color=Gray>table_one</font> <font color=green><b>AS</b></font> <font color=DarkSlateGray><b>table_one_alias</b></font>
    <font color=green><b>JOIN</b></font> <font color=Gray>table_two</font> <font color=green><b>AS</b></font> <font color=DarkSlateGray><b>table_two_alias</b></font> <font color=green><b>ON</b></font> <join_condition>
    <font color=green><b>JOIN</b></font> <font color=Gray>table_three</font> <font color=green><b>AS</b></font> <font color=DarkSlateGray><b>table_three_alias</b></font> <font color=green><b>ON</b></font> <join_condition>
    ...
</font>
For [[SQL#Permanent_Table|permanent]], [[SQL#Temporary_Table|temporary]] and [[SQL#Virtual|virtual]] tables aliasing is optional, their original names are usually sufficient to reference columns. However for [[SQL#Derived_Table|derived tables]], the alias is required if we want to refer to column from the derived table:
<span id='Subquery_in_Join'></span><syntaxhighlight lang='sql'>
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;
</syntaxhighlight>
</syntaxhighlight>
=Using the Same Table Twice=
<font color=darkkhaki>TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch05.html#:-:text=Using%20the%20Same%20Table%20Twice</font>
=Self-Joins=
<font color=darkkhaki>
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</font>

Latest revision as of 17:17, 31 May 2024

External

Internal

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