Java Programming Interviews Exposed by Noel Markham

Author:Noel Markham
Language: eng
Format: epub, pdf
Publisher: Wiley Publishing, Inc.
Published: 2014-01-28T16:00:00+00:00

By default, rows from both tables that meet the matching criteria are included in the join. It is possible to specify that all the rows from one or either table from a join are included, filling a missing match with NULLs.

The office_locations table includes some rows that are not referenced at all in the employees table. This implies that, for some reason, the company has an office that is currently empty. The results of Listing 12-1 show no mention of that location.

In the JOIN clause of the query, if you specify LEFT OUTER JOIN instead, the result will include all rows from the left side of the query, filling in NULLs for no matches on the right, and vice versa for RIGHT OUTER JOIN. Listing 12-2 shows a query with a right outer join for matching the employees and office_locations.

Listing 12-2: A right outer join

SELECT * FROM employees RIGHT OUTER JOIN office_locations ON employees.office_location_id = office_locations.office_location_id;

This query returns an entry for every location_name, regardless of whether there is an employee based at that location:

mysql> select employees.name, office_locations.location_name -> from employees -> right outer join office_locations -> on employees.office_location_id = office_locations.office_location_id; +--------------------+---------------+ | name | location_name | +--------------------+---------------+ | Bob Smith | New York | | Alice Smith | New York | | Eric Twinge | New York | | Cassandra Williams | Paris | | Dominic Taylor | Paris | | NULL | London | +--------------------+---------------+ 6 rows in set (0.00 sec)

It is also possible to perform a left and right join simultaneously; filling in NULLs from both tables where no match occurs, with the syntax FULL OUTER JOIN. This is not applicable to the join between employees and office_locations, because office_location_id is a foreign key relationship: It is not possible to have an employee working in a location that does not exist in the database.

Figure 12-1 visualizes the difference between the joins on two tables.

Figure 12-1

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.