SQL Querying Multiple Tables: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
Line 15: Line 15:
<font size=-2>
<font size=-2>
  <b>person</b>
  <b>person</b>
    Column  |  Type 
------------+---------
  id        | integer    <font color=teal># primary key</font>
  name      | text   
  company_id | integer    <font color=teal># foreign key that references company(id)</font>
<b>company</b>
<b>city</b>
   
   
     Column  |        Type           
     Column  |        Type           

Revision as of 20:19, 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.

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.


Examples

All examples provided in this article are based on three tables (person, address and country), with the following schema:

person
   Column   |  Type  
------------+---------
 id         | integer     # primary key
 name       | text    
 company_id | integer     # foreign key that references company(id)


company
city

   Column   |         Type          
------------+------------------------
 id         | smallint              
 name       | character varying(30) 
 birthday   | date                  
 address_id | smallint              

Indexes:
    "person_pkey" PRIMARY KEY (id)
Foreign-key constraints:
    "person_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id)


Foreign key

Join