SQL Querying Multiple Tables
Jump to navigation
Jump to search
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 condition 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.
To join two tables:
SELECT person.name AS name, company.name AS company
FROM person
INNER 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
INNER JOIN company ON person.company_id = company.id
INNER JOIN city ON company.city_id = city.id;
name | company | city ---------+------------+--------------- Alice | Moonphone | San Francisco Bob | Vortextime | New York Charlie | Bluestone | Chicago
Foreign key