Hi joe, In your example it was not much of a problem since you are effectively using a join as well with your reference to the outer query. But if you don't do that subqueries in the select or where part are usually executed per line and hence may run very slow.
So my opinion is: Avoid subqueries in select and where completely and always use joins. This is less error prone and at least you know what the database is doing. Regards Rainer Joe Thurbon wrote: > Re: Select where not exists... > > > On 03/05/2010, at 9:56 PM, Rainer Döbele wrote: > > > Hi Joe, > > > > There is not a lot, but referring the outer query in an inner query is > something you can't do with Empire-db. > > > > However I would not recommend to use subqueries in the where clause > anyway. > > I'd rather solve this with an outer join similar to this: > > > > select t1.id > > from table1 t1 left join > > ( select t2.id > > from table2 t2 > > ) q1 on q1.id = t1.id > > where q1.id is null > > > > This is something you can to by wrapping your DBCommand in a DBQuery > (see advanced samples for an example). > > > > Would that be a suitable solution for your problem? > > Regards > > > > Rainer > > Hi Rainer, > > Thanks very much for your suggestion. I hadn't tried that formulation. > It is approximately the same speed as the one that I posted. > > Just out of interest, is there a quick way to explain why subqueries in > a where clause are to be avoided? > > Cheers, > Joe
