Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Feb 28, 2002

Left joins - Page 3

By Ian Gilfillan

Let's add another record to the tour_guide table.

INSERT into tour_guides(
       employee_number,
       employee_name) 
VALUES(983,'Nelson Jiranga');

Now run the query again:

SELECT DISTINCT 
  tour_expeditions.employee_number,
  employee_name,location_name 
  FROM tour_locations,
       tour_expeditions,
       tour_guides 
  WHERE tour_locations.location_code = tour_expeditions.location_code AND 
        tour_expeditions.employee_number=tour_guides.employee_number

We get identical results

employee_number employee_name location_name
978 Siyabonge Nomvete Table Mountain
923 Tyrone Arendse Robben Island
978 Siyabonge Nomvete Robben Island
982 Mathew Booth Kruger National Park
942 Jean-Marc Ithier St Lucia

This makes sense, as our new tour guide has not yet undertaken any tours. He does not yet appear in the tour_expeditions table, and so the join does not work, as there is nothing in tour_expeditions to join to.

But what if we want all the employees back, regardless of whether they have undertaken a tour or not? We need to explicitly state this, and we do so using a LEFT JOIN (also called a LEFT OUTER JOIN). To introduce the concept, try the following query:

select DISTINCT employee_name 
       from tour_guides 
       LEFT JOIN tour_expeditions ON 
          tour_guides.employee_number = tour_expeditions.employee_number

This returns:

employee_name
Siyabonge Nomvete
Jean-Marc Ithier
Tyrone Arendse
Mathew Booth
Nelson Jiranga

Note the syntax is almost the same, except that the table names are separated by LEFT JOIN, not a comma, and ON is used for the fields to be joined, rather than WHERE.

So, going back to our original question - how do we return the employee numbers, names and locations of all guides, including those who have not yet given a tour. The query is as follows:

SELECT DISTINCT 
       tour_guides.employee_number,employee_name,location_name 
       FROM tour_guides 
       LEFT JOIN tour_expeditions ON 
          tour_guides.employee_number = tour_expeditions.employee_number 
       LEFT JOIN tour_locations ON 
          tour_locations.location_code=tour_expeditions.location_code

This now returns:

employee_number employee_name location_name
978 Siyabonge Nomvete Table Mountain
923 Tyrone Arendse Robben Island
978 Siyabonge Nomvete Robben Island
982 Mathew Booth Kruger National Park
942 Jean-Marc Ithier St Lucia
983 Nelson Jiranga NULL

Note that Nelson Jiranga now appears in the results table, and as he has not yet lead any tours, that field is NULL.



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM