SQL Querying Multiple Tables: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
Line 37: Line 37:
If more than one table appears in the <code>FROM</code> 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.
If more than one table appears in the <code>FROM</code> 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 one of the tables to be specified in the <code>FROM</code> clause. Then, for each additional table, an <code>JOIN</code> clause is added:
The syntax requires that all the tables are specified in the <code>FROM</code> clause. One of the table does not need any qualifiers, then for each additional table, an:
<font>
<font color=green>JOIN</font> <table_name> <font color=green>ON</font> <join_condition>
</font>
 
<font>
<font>
   <font color=green>FROM</font> <table_one>
   <font color=green>FROM</font> <table_one>

Revision as of 21:02, 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             | 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>

 FROM <table_one>
   INNER|OUTER JOIN <table_two> ON <join_condition>
   INNER|OUTER JOIN <table_three> ON <join_condition>
   ...

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

Join