it is possible to difference within 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>
Yes, you will find variations within the semantics of the Standard outer join as well as an Informix-style outer join which inevitably implies that you will find variations within the query plan.
Generally, make use of the Standard notation for just about any new or modified code - leave the Informix-style outer join notation for (unchanged) legacy code, and ideally update even that to make use of the brand new join notations.
What's the difference? Fair question - difficult to explain, and harder still to develop a great (plausible example). Essentially, the Informix-style notation preserves the rows in the 'dominant' tables (the non-outer tables) even if you will find criteria in line with the values within the outer-became a member of table that will reject individuals rows.
Both of these queries make 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;
Both of these queries don't always make 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 main difference happens in times such as this:
DominantTable OuterJoinedTable pk_column other_column fk_column alt_column 1 twenty 1 3
The conventional LEFT OUTER JOIN notation will make the empty set because. The Informix-style join will make the result:
pk_column other_column fk_column alt_column 1 twenty null null
The information from DominantTable wasn't declined due to a filter condition around the dominant table, so it's maintained by Informix. The conventional join does the outer join after which filters the end result.
Make sure discover, or publish the execution plans for and we'll help digest them.
In other current database engines they can lead to exactly the same execution intend to due optimisation.