Almost all Transact-SQL outer joins written for earlier versions of Adaptive Server that are run on version 12.0 and later produce the same result set. However, there is a category of outer join queries for which the 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 later versions 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 versions of Adaptive Server earlier than 12.0 were evaluated according to these 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,
start it with trace flag 4413 and run any queries that you think
may be join-order-dependent in versions earlier than 12.0. You see a
message similar to the following when you run a join-order-dependent query:
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.
Resolve dependencies your applications have on result sets of join-order queries produced versions earlier than 12.0.
Generally, you will not have any problem from join-order-dependent queries because predicates typically reference only:
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. Transact-SQL queries that have any of the following characteristics, however, 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.
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)
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’)
And produces this 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.
This is the ANSI equivalent query:
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’)
The query is join-order-dependent for the same reason as the previous 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
This is the ANSI equivalent query:
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
