[TransWarp] Towards a query theory, part 6: building queries from filters

[Phillip J. Eby]

At 11:55 AM 10/17/03 +0300, alexander smishlajev wrote:
>Phillip J. Eby wrote, at 17.10.2003 5:29:
>
>>Rather than using EXISTS(SELECT ...) for subqueries, we will use 
>>IN(SELECT ...).  This appears to be enormously easier to do correctly.
>
>are you sure that IN may substitute EXISTS?

Perhaps not in general, but for what we will use it for in the filtering 
language.  In the filtering language, we use it to express "exists x: <some 
criteria about x>" conditions, which may be rewritten as "x in (select ... 
some criteria about x)".


>   here's an example:
>
>  select * from table1 x where exists
>   (select * from table2 y where y.a = x.a and y.b = x.b)

select * from table1 x where x.a in (select a from table2 y where y.b = 
x.b) would work.  Hmm.  You've just made me see that compound keys can be 
handled for subqueries by just moving all but one of the join conditions 
into the subquery.  Thanks!  :)



>>* We've ignored compound keys and ternary (or higher-arity) relationships 
>>in discussion so far.  Our main use cases may not include these as a 
>>requirement, though.  Compound keys can't cleanly be used for IN (SELECT 
>>...) subqueries.
>
>ah, well, the above example looks like compound key.  ok, another example:
>
>  select x.*, y.* from table1 x, table2 y
>   where x.a = y.a and exists
>    (select * from table3 z where z.b = x.b and z.c = y.c)

This can also be rewritten as an IN, in two different ways.

and x.b in (select b from table3 z where z.c = y.c)

and y.c in (select c from table3 z where z.b = x.b)

So in general, they can be rewritten.  In practice, for what I have in 
mind, we will just always be generating them as IN in the first place.  In 
fact, it's possible I won't include an EXISTS operator in the initial 
relational algebra package.