AW: SQL - minus

Hi, the statement for informix must be like this: select e.code from table_1 e where e.code like 'D%' and Not Exists ( select g.code from table_2 g where g.code = e.code); Martin >>> "Pietje Puk" <Pietje@puk.com> 08.08.00 12:02 >>> Hi All, I've got two customertables, just a little bit different. I want to know/see only the custmores that i am missing in the second table. In Oracle or SQL-server the syntax is something like: select e.code from table_1 e where e.code like 'D%'minus select g.code from table_2 g where g.code like 'D%' What is the syntax for "MINUS" in Informix? Or doesn't Informix support this? Allready thanks Alex

Is EXISTS faster than IN ? why ? Manel Falcó On Tue, 08 Aug 2000 14:07:58 +0200, "Martin Fey" <Martin.Fey@bluvo.de> wrote: > >Hi, > >the statement for informix must be like this: > >select e.code >from table_1 e >where e.code like 'D%' > and Not Exists ( > select g.code > from table_2 g > where g.code = e.code);> > >Martin > > > >

In my experience, EXISTS tends to be faster than IN, but that (like most things in the database world) is not a guarantee. It depends on what the matching conditions are and how they are implemented (indexes, etc) and on table size. Generally, IN results in a preliminary query to build a temporary table (in memory if possible). Then the original SELECTED table is processed and a lookup (probably via sequential scan or hash -- if you're lucky) determines the IN condition result. EXISTS, on the other hand, usually performs a random lookup as it processes each row of the original table. No data needs to be returned, only a 'yes/no' result, as compared to the IN, which is returning a set of data values. Where a large table exists, with properly constructed indexes, I have seen the EXIST give significant improvement over IN. This is even more evident when the percentage of the table being used (accessed by the IN or EXISTS) is small. Again, this information is based only on my own experience with DB2 and Informix (which seem to cost optimize similarly). Your mileage may vary. HTH, Doug "Manel Falc' i Aige" <manel@semic.es> wrote in message news:3995dc54.13718625@news.bcn.ttd.net... > Is EXISTS faster than IN ? why ? > > Manel Falc' > > On Tue, 08 Aug 2000 14:07:58 +0200, "Martin Fey" <Martin.Fey@bluvo.de> > wrote: > > > > >Hi, > > > >the statement for informix must be like this: > > > >select e.code > >from table_1 e > >where e.code like 'D%' > > and Not Exists ( > > select g.code > > from table_2 g > > where g.code = e.code);> > > > > >Martin > > > > > > > > >