By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,948 Members | 1,595 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,948 IT Pros & Developers. It's quick & easy.

Thinking in Sets versus Procedural code

P: n/a
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?

Apr 11 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
--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
Apr 11 '07 #2

P: n/a
On Apr 11, 5:56 pm, Lennart <erik.lennart.jons...@gmail.comwrote:
--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.

Apr 13 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.