SQL Querying Multiple Tables: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
Line 86: Line 86:
The SQL syntax permits to alias each of the join-participating tables, so you can identify which tables you are referring to when you reference columns in the <code>SELECT</code> clause, by using [[SQL_SELECT#Table_Aliases|table aliases]]:
The SQL syntax permits to alias each of the join-participating tables, so you can identify which tables you are referring to when you reference columns in the <code>SELECT</code> clause, by using [[SQL_SELECT#Table_Aliases|table aliases]]:
<font size=-1.5>
<font size=-1.5>
   <font color=green><b>FROM</b></font> <font color=Gray>table_one</font> <font color=green><b>AS</b></font> <font color=DarkSlateGray>table_one_alias</font>
   <font color=green><b>FROM</b></font> <font color=Gray>table_one</font> <font color=green><b>AS</b></font> <font color=DarkSlateGray><b>table_one_alias</b></font>
     <font color=green><b>JOIN</b></font> <font color=Gray>table_two</font> <font color=green><b>AS</b></font> <font color=DarkSlateGray>table_two_alias</font> <font color=green><b>ON</b></font> <join_condition>
     <font color=green><b>JOIN</b></font> <font color=Gray>table_two</font> <font color=green><b>AS</b></font> <font color=DarkSlateGray><b>table_two_alias</b></font> <font color=green><b>ON</b></font> <join_condition>
     <font color=green><b>JOIN</b></font> <font color=Gray>table_three</font> <font color=green><b>AS</b></font> <font color=DarkSlateGray>table_three_alias</font> <font color=green><b>ON</b></font> <join_condition>
     <font color=green><b>JOIN</b></font> <font color=Gray>table_three</font> <font color=green><b>AS</b></font> <font color=DarkSlateGray><b>table_three_alias</b></font> <font color=green><b>ON</b></font> <join_condition>
     ...
     ...
</font>
</font>

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

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 mechanism of linking two or more tables is referred to as a join. 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>

is added as in the following example:

 FROM table_one
   JOIN table_two ON <join_condition>
   JOIN table_three ON <join_condition>
   ...

Note that a JOIN keyword without any qualifier defaults to an inner join.

To join two tables:

 SELECT person.name AS name, company.name AS company
 FROM person 
   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
   JOIN company ON person.company_id = company.id 
   JOIN city ON company.city_id = city.id;

  name   |  company   |     city
---------+------------+---------------
 Alice   | Moonphone  | San Francisco
 Bob     | Vortextime | New York
 Charlie | Bluestone  | Chicago

Using Table Aliases in Joins

The SQL syntax permits to alias each of the join-participating tables, so you can identify which tables you are referring to when you reference columns in the SELECT clause, by using table aliases:

 FROM table_one AS table_one_alias
   JOIN table_two AS table_two_alias ON <join_condition>
   JOIN table_three AS table_three_alias ON <join_condition>
   ...

For permanent, temporary and virtual tables aliasing is optional, as their original names can be used to reference columns. However for derived tables, the alias is useful:

 FROM <table_one> AS table_one_alias
   JOIN <table_two> AS table_two_alias ON <join_condition>
   JOIN <table_three> AS table_three_alias ON <join_condition>
   ...

Foreign key

Join Types

Inner Join

Code JOIN is specified without any qualifier, it implies an inner join. For clarity, INNER can be specified:

 SELECT ...
 FROM ...
   INNER JOIN sometable ON ...