SQL Querying Multiple Tables
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.
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 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
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. For two tables with N and M rows respectively, the result will contain 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.
If more than one table appears in the FROM
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 syntax requires that all the tables are specified in the FROM
clause. One of the table does not need any qualifiers, then for each additional table, an:
JOIN table_name ON <join_condition>
is added as in the following example:
FROM table_one JOIN table_two ON <join_condition> JOIN table_three ON <join_condition> ...
Note that a JOIN
keyword without any qualifier defaults to an inner join.
To inner join two tables:
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
To inner join three tables:
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
Using Table Aliases in Joins
The SQL syntax permits to alias each of the join-participating tables, so you can identify which tables you are referring to when you reference columns in the SELECT
clause, by using table aliases:
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 sufficient to reference columns. However for derived tables, the alias is useful:
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;
Join Condition
Tables are usually joined using foreign keys. The 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.
Join Types
Inner Join
When JOIN
is specified without any qualifier, it implies an inner join. For clarity, INNER
can be specified:
SELECT ...
FROM ...
INNER JOIN sometable ON ...
Cross Join
A cross join produces the cartesian product of the tables involved in join.
For two tables:
SELECT * FROM person CROSS JOIN company;
id | name | company_id | eye_color | id | name | city_id ----+---------+------------+-----------+----+------------+--------- 1 | Alice | 10 | blue | 10 | Moonphone | 100 2 | Bob | 20 | black | 10 | Moonphone | 100 3 | Charlie | 30 | black | 10 | Moonphone | 100 1 | Alice | 10 | blue | 20 | Vortextime | 200 2 | Bob | 20 | black | 20 | Vortextime | 200 3 | Charlie | 30 | black | 20 | Vortextime | 200 1 | Alice | 10 | blue | 30 | Bluestone | 300 2 | Bob | 20 | black | 30 | Bluestone | 300 3 | Charlie | 30 | black | 30 | Bluestone | 300 (9 rows)