CODEWITHSUNDEEP
databaseinformixouter-join
kubek2k
kubek2k

Reputation: 5814

Difference in the query plan when using ANSI OUTER JOIN and OUTER in Informix

is there a difference in the Informix query performance using ANSI syntax:

SELECT .. 
  LEFT OUTER JOIN some_table ON (<condition>) 
  LEFT OUTER JOIN some_other_table (<condition_on_some_table>)

and Informix specific OUTER syntax:

SELECT ... 
  OUTER (some_table, 
   OUTER(some_other_table)) 
WHERE <join_conditions>

thanks

Upvotes: 2

Views: 2268

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753585

Yes, there are differences in the semantics of a Standard outer join and an Informix-style outer join which inevitably means that there are differences in the query plan.

In general, use the Standard notation for any new or modified code - leave the Informix-style outer join notation for (unchanged) legacy code, and preferably update even that to use the new join notations.

What is the difference? Fair question - hard to explain, and harder still to come up with a good (plausible example). Basically, the Informix-style notation preserves the rows from the 'dominant' tables (the non-outer tables) even when there are criteria based on the values in the outer-joined table that would reject those rows.

These two queries produce the same result:

SELECT i.*, o.*
  FROM DominantTable AS i, OUTER OuterJoinedTable AS o
 WHERE i.pk_column = o.fk_column;

SELECT i.*, o.*
  FROM DominantTable AS i
  LEFT OUTER JOIN OuterJoinedTable AS o
    ON i.pk_column = o.fk_column;

These two queries do not necessarily produce the same result:

SELECT i.*, o.*
  FROM DominantTable AS i, OUTER OuterJoinedTable AS o
 WHERE i.pk_column = o.fk_column
   AND (o.alt_column IS NULL OR o.alt_column = 1);

SELECT i.*, o.*
  FROM DominantTable AS i
  LEFT OUTER JOIN OuterJoinedTable AS o
    ON i.pk_column = o.fk_column
 WHERE (o.alt_column IS NULL OR o.alt_column = 1);

The difference occurs in a situation like this:

DominantTable                              OuterJoinedTable
pk_column   other_column                   fk_column   alt_column
1           twenty                         1           3

The standard LEFT OUTER JOIN notation will produce the empty set as the result. The Informix-style join will produce the result:

pk_column   other_column   fk_column   alt_column
1           twenty         null        null

The data from DominantTable was not rejected because of a filter condition on the dominant table, so it is preserved by Informix. The standard join does the outer join and then filters the result.

Upvotes: 3

xQbert
xQbert

Reputation: 35323

Test and find out, or post the execution plans for both and we'll help digest them.

In other current database engines they will result in the same execution plan to due optimization.

Upvotes: 0

Related Questions