Left joins - Page 3

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

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:

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:

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