Converting outer joins with join-order dependency

Almost all Transact-SQL outer joins written for earlier versions of Adaptive Server that are run on a version 12.0 and later Adaptive Server produce the same result set. However, there is a category of outer join queries whose result sets depend on the join order chosen during optimization. Depending on where in the query the predicate is evaluated, these queries may produce different result sets when they are issued using the current version of Adaptive Server. The result sets they return are determined by the ANSI rules for assigning predicates to joins.

Predicates cannot be evaluated until all the tables they reference are processed. That is, in the following query the predicate “and titles.price > 20” cannot be evaluated until the titles table is processed:

select title, price, au_ord
from titles, titleauthor
where titles.title_id *= titleauthor.title_id
and titles.price > 20

Predicates in earlier versions of Adaptive Server were evaluated according to the following semantics:

If the predicate was evaluated on the inner table of an outer join, the predicate had on clause semantics.
If the predicate was evaluated with a table that is outer to all outer joins, or is join-order independent, the predicate had where clause semantics.
Before you run Adaptive Server in a production environment, make sure you start it with traceflag 4413 and run any queries that you think may be join-order dependent in pre-12.0 versions of Adaptive Server. Adaptive Server started with trace flag 4413 issues a message similar to the following when you run a query that is join-order dependent in a pre-12.0 version of Adaptive Server:
```
Warning: The results of the statement on line %d are join-order independent. Results may differ on pre-12.0 releases, where the query is potentially join-order dependent.
```
Make sure you resolve dependencies your applications have on result sets of join-order queries produced by pre-12.0 Adaptive Server.

When do join-order dependent outer joins affect me?

Generally, you will not have any problem from join-order dependent queries because predicates typically only reference:

An outer table, which is evaluated using where clause semantics
An inner table, which is evaluated using on clause semantics
The inner table and tables upon which the inner table is dependent

These do not produce join-order dependent outer joins. However, Transact-SQL queries that have any of the following characteristics may produce a different result set after they are translated to an ANSI outer join:

Predicates that contain or statements and reference an inner table of an outer join and another table that this inner table is not dependent on
Inner table attributes that are referenced in the same predicate as a table which is not in the inner table’s join-order dependency
An inner table referenced in a subquery as a correlated reference

The following examples demonstrate the issues of translating Transact-SQL queries with join-order dependency to ANSI outer join queries.

Example:

select title, price, authors.au_id, au_lname
from titles, titleauthor, authors
where titles.title_id =* titleauthor.title_id
and titleauthor.au_id = authors.au_id
and (titles.price is null or authors.postalcode = ’94001’)

This query is join-order independent because the outer join references both the titleauthor and the titles tables, and the authors table can be joined with these tables according to three join orders:

authors, titleauthors, titles (as part of the on clause)
titleauthors, authors, titles (as part of the on clause)
titleauthors, titles, authors (as part of the where clause)

This query produces the following message:

Warning: The results of the statement on line 1 are join-order independent. Results may differ on pre-12.0 releases, where the query is potentially join-order dependent. Use trace flag 4413 to suppress this warning message.

Following is the ANSI equivalent:

select title, price, authors.au_id, au_lname
from titles right join
(titleauthor inner join authors
on titleauthor.au_id = authors.au_id)
on titles.title_id = titleauthor.title_id
where (titles.price is null or authors.postalcode = ’94001’)

Another example:

select title, au_fname, au_lname, titleauthor.au_id, price
from titles, titleauthor, authors
where authors.au_id *= titleauthor.au_id
and titleauthor.au_ord*titles.price > 40

The query is join-order dependent for the same reason as the previous example. Here is the ANSI equivalent:

select title, au_fname, au_lname, titleauthor.au_id, price
from titles,(authors left join titleauthor
on titleauthor.au_id = authors.au_id)
where titleauthor.au_ord*titles.price > 40