Re: query results

Jean: If you need to know what rows in either table have no match in the other you have to use a UNION. (It is because such a construct is functionally and semantically equivalent to a 'both' outer join that Informix only supports one-way outer joins.) So: SELECT ... FROM tab1 AS a LEFT OUTER JOIN tab2 AS b ON a.key1 = b.key1 ... WHERE b.key1 IS NULL UNION SELECT ... FROM tab2 AS b LEFT OUTER JOIN tab1 AS a ON a.key1 = b.key1 ... WHERE a.key1 IS NULL; Art S. Kagel ----- Original Message ----- From: Jean Sagi <jeansagi@myrealbox.com> At: 11/17 17:00 > Thanks Art and "Whatever...", for your comments... > > I'm aware of LEFT OUTER JOIN in IDS 9.3 and 9.4 (althoug it would be > great if FULL OUTER join exists... but that's another story). > > But there is something funny... I thought that the left outer join with > null filters were not fixed on 9.3 and 9.4... > > I tryed the approach "whatever" shows some months ago but I only get > nulls on the right table... ie: > > ... > from a left outer join b on (a.x = b.x) > where b.y is null > ... > > I also tryed > > ... > from a left outer join b on (a.x = b.x and b.y is null) > where b.x is null > ... > > Maybe I do something wrong... > > I'll try with "wahtever" notes.... > > > Art S. Kagel wrote: > > On Sun, 16 Nov 2003 05:54:29 -0500, Jean Sagi wrote: > > > > > > > >>Art S. Kagel wrote: > >> > >> > >>>... "EXISTS sub-query" is a correlated sub-query which is always slower than > >>>a join and can "always [can] be converted into a join"... > >> > >>EXISTS sub-query always [can] be converted into a join... I can imgine how to > >>do it. > >> > >>But > >> > >>Can, NOT EXISTS sub-query, be converted into a join? > >> > >>If so, how? (I can use this...) > >> > > > > WHATEVER has nailed the answer, except that IDS supports LEFT OUTER joins not > > RIGHT ones and it does not support sub-query-instead-of-table in the FROM > > clause. As is stated in that message you use ANSI-92 syntax for a LEFT OUTER > > JOIN with the join conditions in the ON clause and a filter for a NULL value > in > > some NOT NULL column in the dependent table (as WHATEVER implies one of the > > JOIN columns is an excellent candidate for the filter. > > > > Art S. Kagel > > > >>Chucho! > >> > > > > > > > > -- > > > Atte, > > > Jes's Antonio Santos Giraldo > jeansagi@myrealbox.com > jeansagi@netscape.net sending to informix-list