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 Condition
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:
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 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 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
Foreign key