RE: How can i use 'outer join'?
Jou can use left outer join according to both the ANSI and informix syntax:
ANSI Syntax:
select *
from table1 left outer join table2 on table1.field1 = table2.field2
where ....
Informix Syntax:
select *
from table1, outer table2
where table1.field1 = table2.field2
The two different syntax are not equal when the where part contains filters
for table2 table's fields.
The ANSI syntax use post join filter. It means that performs the join and
filter the result.
The Informix syntax use pre join filter. It means that performs the filter
on the table2 table first and then perform the outer join.
For example
select *
from table1 left outer join table2 on table1.field1 = table2.field2
where table2.field2 is null
selects the rows from table1 and not in table2.
select *
from table1, outer table2
where table1.field1 = table2.field2 and table2.field2 is null
selects all of the rows from table1 and the corresponding values from table2
where table2.field2 is null
It differs!
I prefer the ANSI syntax, as informix says too.
ANSI syntax works on IDS 7.31 and IDS.2000 too, but does not work IDS 7.30
or above
-----Eredeti 'zenet-----
Felad': Kwangsoo Chae [mailto:cks@dblab.knu.ac.kr]
Elk'ldve: 2001. janu'r 3. 8:48
C'mzett: informix-list@iiug.org
T'rgy: Q: How can i use 'outer join'?
Are there left, right, and full outer join in INFORMIX?
How can i use that?