Thinking in Sets versus Procedural code

[--CELKO--]

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?

[Lennart]

--CELKO-- wrote:

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?

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

[--CELKO--]

On Apr 11, 5:56 pm, Lennart <[e-mail]:

--CELKO-- wrote:

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?

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

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.