Re: query results

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...) Chucho! -- Atte, Jes's Antonio Santos Giraldo jeansagi@myrealbox.com jeansagi@netscape.net sending to informix-list

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? Not Exists can be rewritten using SQL-92 "outer join" syntax, which in informix is limited to only right outer joins (I think). Doing this usually results in a performance improvement, but YMMV. This will return a set of keys that exist in containing_table that do not exist in not_exists_table: select containing_table.joining_column from not_exists_table right outer join containing_table on not_exists_table.joining_column = containing_table.joining_column where not_exists_table.joining_column is null Of course, the tables can be (queries) instead of actual tables, and you could use using (joining_column) instead of "on equi-join clause" Do an advanced groups.google.com search on this newsgroup to search for results and examples (like this search): http://tinyurl.com/v8v7 The actual URL is this, in case the above shorter one doesn't work for you: http://www.google.com/groups?as_epq=outer%20join&as_oq=left%20right&safe=images&ie=UTF-8&oe=UTF-8&as_ugroup=comp.databases.informix&lr=&hl=en

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! >