SQL JOIN Multiple Tables

Using JOIN with more than two tables.

If you want to get data from the tables : Customers, Addresses, Telephones and US_States, you can do that in one SELECT statement using JOIN.

Here is an example of that using INNER JOIN :
SQL server, MySQL, Oracle, PostgreSQL :
select CONCAT(Last_name, CONCAT(' ',First_name)) as Name,
   Telephone_type "Phone type",
   Telephone_number as "Phone number",
   address_type as "Address type",
   us_state_nm as "USA State"
 FROM customers  c
 JOIN telephones t ON (c.Customer_Id=t.Customer_Id)
 JOIN Addresses a ON (c.Customer_Id=a.Customer_Id)
 JOIN US_states u ON (a.US_State_cd=u.US_State_cd)
 where address_type ='Shipping'
 ORDER BY NAME, Telephone_type;
The result should be:
name Phone type Phone number Address type USA State
Bowman Judith Work (371) 143-54 Shipping New York
Bowman Judith Cell (371) 153-54 Shipping New York
Darnovsky Marcy Unknown Shipping Massachusetts
Darnovsky Marcy Unknown (881) 141-54 Shipping Massachusetts
Emerson Sandra Unknown Shipping Arkansas
Emerson Sandra Unknown (881) 141-54 Shipping Arkansas
Nixon Ricard Home (978) 667-94 Shipping Florida
Nixon Ricard Work (978) 667-94 Shipping Florida
Redford Robert Unknown (678) 223-94 Shipping California
Redford Robert Home (679) 234-94 Shipping California
Here is an example of that using WHERE statement :
SQL server, MySQL, Oracle, PostgreSQL :
select CONCAT(Last_name, CONCAT(' ',First_name)) as Name,
   Telephone_type "Phone type",
   Telephone_number as "Phone number",
   address_type as "Address type",
   us_state_nm as "USA State"
 FROM customers  c, telephones t, Addresses a ,US_states u
 WHERE c.Customer_Id=t.Customer_Id
 AND c.Customer_Id=a.Customer_Id
 AND a.US_State_cd=u.US_State_cd
 AND address_type ='Shipping'
 ORDER BY NAME, Telephone_type;
The result should be:
name Phone type Phone number Address type USA State
Bowman Judith Work (371) 143-54 Shipping New York
Bowman Judith Cell (371) 153-54 Shipping New York
Darnovsky Marcy Unknown Shipping Massachusetts
Darnovsky Marcy Unknown (881) 141-54 Shipping Massachusetts
Emerson Sandra Unknown Shipping Arkansas
Emerson Sandra Unknown (881) 141-54 Shipping Arkansas
Nixon Ricard Home (978) 667-94 Shipping Florida
Nixon Ricard Work (978) 667-94 Shipping Florida
Redford Robert Unknown (678) 223-94 Shipping California
Redford Robert Home (679) 234-94 Shipping California
Here is an example of that using NATURAL JOIN :
SQL server does not support NATURAL JOIN.
MySQL, Oracle, PostgreSQL :
select CONCAT(Last_name, CONCAT(' ',First_name)) as Name,
   Telephone_type "Phone type",
   Telephone_number as "Phone number",
   address_type as "Address type",
   us_state_nm as "USA State"
 from customers natural join telephones
               natural join Addresses
               natural join US_states 
 where address_type ='Shipping'
 ORDER BY NAME, Telephone_type;
The result should be:
name Phone type Phone number Address type USA State
Bowman Judith Work (371) 143-54 Shipping New York
Bowman Judith Cell (371) 153-54 Shipping New York
Darnovsky Marcy Unknown Shipping Massachusetts
Darnovsky Marcy Unknown (881) 141-54 Shipping Massachusetts
Emerson Sandra Unknown Shipping Arkansas
Emerson Sandra Unknown (881) 141-54 Shipping Arkansas
Nixon Ricard Home (978) 667-94 Shipping Florida
Nixon Ricard Work (978) 667-94 Shipping Florida
Redford Robert Unknown (678) 223-94 Shipping California
Redford Robert Home (679) 234-94 Shipping California
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.