SQL Querying Multiple Tables: Difference between revisions
Jump to navigation
Jump to search
| Line 12: | Line 12: | ||
Column | Type id | name | company_id | Column | Type id | name | company_id | ||
-----------+--------- ------+------------------+------------- | -----------+--------- ------+------------------+------------- | ||
id | integer <font color=teal># primary key</font> | id | integer <font color=teal># primary key</font> 1 | Alice | 10 | ||
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 | ||
| Line 18: | Line 18: | ||
<b>company</b> | <b>company</b> | ||
Column | Type | Column | Type id | name | company_id | ||
-----------+--------- | -----------+--------- ------+------------------+------------- | ||
id | integer <font color=teal># primary key</font> | id | integer <font color=teal># primary key</font> 1 | Alice | 10 | ||
name | text | name | text 2 | Bob | 20 | ||
city_id | integer <font color=teal># foreign key that references city(id)</font> | city_id | integer <font color=teal># foreign key that references city(id)</font> 3 | Charlie | 30 | ||
<b>city</b> | <b>city</b> | ||
Column | Type | Column | Type id | name | company_id | ||
-----------+--------- | -----------+--------- ------+------------------+------------- | ||
id | integer <font color=teal># primary key</font> | id | integer <font color=teal># primary key</font> 1 | Alice | 10 | ||
name | text | name | text 2 | Bob | 20 | ||
3 | Charlie | 30 | |||
</font> | </font> | ||
Revision as of 20:43, 23 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.
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 | company_id
-----------+--------- ------+------------------+-------------
id | integer # primary key 1 | Alice | 10
name | text 2 | Bob | 20
city_id | integer # foreign key that references city(id) 3 | Charlie | 30
city
Column | Type id | name | company_id
-----------+--------- ------+------------------+-------------
id | integer # primary key 1 | Alice | 10
name | text 2 | Bob | 20
3 | Charlie | 30
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