SQL Querying Multiple Tables: Difference between revisions

From NovaOrdis Knowledge Base
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>.                              1  | Alice            | 10
  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

Join