Select where not exists...

[Joe Thurbon <jo...@intersect.org.au>]

First, thanks for a terrific library. It's really nice!

I have a problem, though. Here's a simplified version.

Consider two tables:
 table1 with a single column 'id'
 table2 with a single column 'id'

I'd like to find all of the entries in table one that are not in table two.

After a lot of profiling with various databases, the query I want to generate is

select t1.id 
from table1 t1 
where not exists
(
   select t2.id 
   from table2 t2
   where t1.id = t2.id
)

Note that the inner query refers to the outer table t1 on the second last line.

I thought that the empire-db construction would be something like:

Table1 table1 = ...
Table2 table2 = ...

DBCommand subSelect  = db.createCommand();
subSelect.select(table2.id);
subSelect.where(table1.id.is(table2.id));

DBCommand topSelect  = db.createCommand();
topSelect.select(table1.id);
topSelect.where(new DBExistsExpr(subSelect).not());

However, this generates the query 

select t1.id 
from table1 t1 
where not exists
(
   select t2.id 
   from table2 t2, t1
   where t1.id = t2.id
)

The difference is the second last line, where t1 is added to the from list of the sub-select. Which basically means that the 'not-exists' evaluates to 'false'. 

Any hints or clues?

Cheers,
Joe


--
Dr Joe Thurbon | eResearch Analyst | Intersect
joe.thurbon@intersect.org.au | www.intersect.org.au
T: +61 2 8079 2535 | M: +61 413 609 094 
Level 12, 309 Kent St, Sydney NSW 2000, Australia

re: Select where not exists...

[Rainer Döbele <do...@esteam.de>]

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

Re: Select where not exists...

[Joe Thurbon <jo...@intersect.org.au>]

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

re: Select where not exists...

[Rainer Döbele <do...@esteam.de>]

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

where not exists
> 

Joe Thurbon wrote:
> re: Select where not exists...
> 
> First, thanks for a terrific library. It's really nice!
> 
> I have a problem, though. Here's a simplified version.
> 
> Consider two tables:
>  table1 with a single column 'id'
>  table2 with a single column 'id'
> 
> I'd like to find all of the entries in table one that are not in table
> two.
> 
> After a lot of profiling with various databases, the query I want to
> generate is
> 
> select t1.id
> from table1 t1
> where not exists
> (
>    select t2.id
>    from table2 t2
>    where t1.id = t2.id
> )
> 
> Note that the inner query refers to the outer table t1 on the second
> last line.
> 
> I thought that the empire-db construction would be something like:
> 
> Table1 table1 = ...
> Table2 table2 = ...
> 
> DBCommand subSelect  = db.createCommand();
> subSelect.select(table2.id);
> subSelect.where(table1.id.is(table2.id));
> 
> DBCommand topSelect  = db.createCommand();
> topSelect.select(table1.id);
> topSelect.where(new DBExistsExpr(subSelect).not());
> 
> However, this generates the query
> 
> select t1.id
> from table1 t1
> where not exists
> (
>    select t2.id
>    from table2 t2, t1
>    where t1.id = t2.id
> )
> 
> The difference is the second last line, where t1 is added to the from
> list of the sub-select. Which basically means that the 'not-exists'
> evaluates to 'false'.
> 
> Any hints or clues?
> 
> Cheers,
> Joe
> 
> 
> --
> Dr Joe Thurbon | eResearch Analyst | Intersect
> joe.thurbon@intersect.org.au | www.intersect.org.au
> T: +61 2 8079 2535 | M: +61 413 609 094
> Level 12, 309 Kent St, Sydney NSW 2000, Australia