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.

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         | integer # primary key
name       | text    
city_id    | integer # foreign key that references city(id)

city

Column     | Type   
-----------+---------
id         | integer # primary key
name       | text    

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.

 SELECT person.name, company.name
 FROM person 
   INNER JOIN company ON person.id = company.id

Foreign key