Multiple outer joins

The Problem

Many database management systems do not allow tables to be joined to the result of an outer join.

Notes

I'm using the SQL-92 notation for outer joins in the examples, despite my preference for the Sybase notation. You should always use the SQL-92 notation in Microsoft SQL Server, because Microsoft plan to remove the Sybase notation. For your information the equivalent Sybase notations are listed below.

• LEFT OUTER JOIN is the equivalent of *=

Solution 1

Create a view, including the fields from the tables which formed the outer join. If you have multiple outer joins you can include the previous view in your new view. I would also suggest creating a view encapsulating all the views created to achieve the desired result set.

Example 1

Quick Table Definition:

Table employee: employee_id (primary key), employee_name, company_id (references company)
Table company:  company_id  (primary key), company_name,  country_id (references country)
Table country:  country_id  (primary key), country_name

Rules:

The employees can belong to 0 or 1 companies.
The companies can belong to 0 or 1 countries.

Query:

To obtain a listing of employees relating to companies, including those which do not belong to a company, we need to perform an outer join.

SELECT employee.employee_id,
       employee.employee_name,
       company.company_id,
       company.company_name,
       company.country_id
FROM   employee LEFT OUTER JOIN company
       ON employee.employee_id = company.company_id

Using views:

We can quite happily use this query anywhere to get the information we want, however, what if we're also interested in the country that the company belongs to? As mentioned most database systems will not allow a further outer join, so we have to "trick" the database system into allowing the extra outer join. We do this by using views.

CREATE VIEW employee_comp_vw
AS
SELECT employee.employee_id,
       employee.employee_name,
       company.company_id,
       company.company_name,
       company.country_id
FROM   employee LEFT OUTER JOIN company
       ON employee.employee_id = company.company_id

CREATE VIEW employee_comp_cnty_vw
AS
SELECT employee_comp_vw.employee_id,
       employee_comp_vw.employee_name,
       employee_comp_vw.company_id,
       employee_comp_vw.company_name,
       country.country_id,
       country.country_name
FROM   employee_comp_vw LEFT OUTER JOIN country
       ON employee_comp_vw.country_id = country.country_id

The employee_comp_cnty_vw may now be used to query the data required, despite the fact that the database system will not let you do this directly. It also has the added benefit that you have defined the relationship between the tables, as well as the obvious security advantages of using views.

Pitfalls

Database servers do not like outer joins, period. Try to keep outer joins to a minimum, particularly for high load servers. Also try to limit the number of layers of outer joins, using the method described. Sometimes there are other options, which may provide a better solution, this leads us on to solution 2.

Solution 2

It is possible to form more than 1 query, outputting the result of each outer join query to a temporary table. In some database systems this is actually quicker than the above solution. In most database systems it is possible to encapsulate this functionality within a stored procedure, however, the result set from a stored procedure is not always usable by all systems.

Example 2

This is an alternative solution to Example 1.

Solution to example 1 using temporary tables:

SELECT employee.employee_id,
       employee.employee_name,
       company.company_id,
       company.company_name,
       company.country_id
INTO   #employee_storage
FROM   employee LEFT OUTER JOIN company
       ON employee.employee_id = company.company_id

SELECT #employee_storage.employee_id,
       #employee_storage.employee_name,
       #employee_storage.company_id,
       #employee_storage.company_name,
       country.country_id,
       country.country_name
FROM   #employee_storage LEFT OUTER JOIN country
       ON #employee_storage.country_id = country.country_id

Pitfalls

This is not a very portable solution, it doesn't promote reuse of code. Should a part of the database structure change, you may need to change this code in many places. There are ways of improving this situation, such as encapsulating the functionality in a stored procedure. You may also use permanent tables instead of temporary tables (not recommended to be honest).

Which Solution?

My preference is for Solution 1 in most cases, because it allows complete encapsulation and control over the data available. However, in some circumstances, a performance gain can be achieved by using Solution 2. Which method you choose really depends on performance requirements vs. usability.

Details

Author:     Wayne Jones
Created:    4 December 2002
Works with: MS-SQL, Sybase, MySQL, others