Re: SQL for intervals

Tonkuma wrote:
> a)
> select id_lwr, min_t as lwr_bound, min(max_t) as upr_bound
> from (
> select id as id_lwr, t as min_t from W W1 where not exists (
> select 1 from W W2 where W1.t = W2.t + 1 and W1.id = W2.id
> )
> ) WL,
> (
> select id as id_upr, t as max_t from W W3 where not exists (
> select 1 from W W4 where W3.t = W4.t - 1 and W3.id = W4.id
> )
> ) WU
> where id_lwr = id_upr and max_t > min_t
> group by id_lwr, min_t
> ;
>
> b)
> SELECT lbound.id
> , CHAR(lbound.t,USA) "LowerBound"
> , CHAR(MIN(ubound.t),USA) "UpperBound"
> FROM W lbound
> , W ubound
> WHERE NOT EXISTS
> (SELECT *
> FROM lower
> WHERE lower.id = lbound.id
> AND lower.t = lbound.t - 1 day
> )
> AND ubound.id = lbound.id
> AND ubound.t >= lbound.t
> AND NOT EXISTS
> (SELECT *
> FROM W upper
> WHERE upper.id = lbound.id
> AND upper.t = ubound.t + 1 day
> )
> GROUP BY
> lbound.id, lbound.t
>
> Compared a) and b), at least b) doesn't need "(select id as id_lwr, t
> as min_t from", "(select id as id_upr, t as max_t from", ") WL" and ")
> WU".

And a) doesn't need (SELECT * FROM W lower...) or (SELECT * FROM W upper...) [Not to mention a) doesn't need NOT EXISTS either.]

I repeat: How is nesting in the where clause any less nesting than in the from clause?

After you finish pondering that question, ask yourself:

Why should we avoid nesting at all? Received on Sat Dec 16 2006 - 14:57:39 CET