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

Phillip J. Eby pje at telecommunity.com
Fri Oct 17 11:44:45 EDT 2003

```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 ...

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

```