Thinking in Sets versus Procedural code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

--CELKO-- - 11 Apr 2007 20:43 GMT

I am trying to collect a few examples of SQL queries that were done
with a "procedural mindset" and another solution done with a "set-
oriented mindset". I have a short article at
http://www.dbazine.com/ofinterest/oi-articles/celko5
with an example of what I want.

Anyone got some examples?

Reply to this Message

Lennart - 11 Apr 2007 23:56 GMT

> I am trying to collect a few examples of SQL queries that were done
> with a "procedural mindset" and another solution done with a "set-
[quoted text clipped - 3 lines]
>
> Anyone got some examples?

Not really, just curious whats wrong with an exists query?

select distinct x.col1, x.col2, x.col3, x.col4
from foobar x
where not exists (
    select 1 from foobar y
    where (x.col1, x.col2, x.col3)
     = (y.col1, y.col2, y.col3)
    and coalesce(y.col4,1) <> 0
)

It seems more straight forward

/Lennart

Reply to this Message

--CELKO-- - 13 Apr 2007 23:17 GMT

> > I am trying to collect a few examples of SQL queries that were done
> > with a "procedural mindset" and another solution done with a "set-
[quoted text clipped - 16 lines]
>
> It seems more straight forward

I like that one, but a lot of SQLs do not have the row constructor and
comparisons. The other advantage of using aggregate functions is that
some products keep MIN(), MAX(), COUNT(), etc. as part of their
statistics so you just look them up and do not have to compute them.

Reply to this Message