Re: how to do 'deep queries'?

From: "Anthony Molinaro" <amolinaro(at)wgen(dot)net>
To: <daryl(at)brandywine(dot)com>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: how to do 'deep queries'?
Date: 2005-09-27 14:23:19
Message-ID: 3C6C2B281FD3E74C9F7C9D5B1EDA4582182583@wgexch01.wgenhq.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Daryl,

> Whether you feel that is unnecessary or not, it *is* the ANSI Standard

> and is thus, by definition, "how queries should be written."

I disagree 100%. Oracle and db2 introduced window functions years
before
Ansi added them. Should we not have used them? It absurd to avoid using
a feature cuz it's not ansi.

Honestly, Don't be a slave to ansi, you miss out on all the great
vendor specific functionality *that you're already paying for*

> it was added to make the *intention* of the query clearer.

More clearer to whom?

Certainly not developers who have been working for many years
using the old syntax.

The intention of the old syntax is perfect. Realize that the problem is
not the old syntax, the problem is the watered down database field
today.
I see this more and more with each interview I conduct looking
for dba's and developers.

You know, it used to be that database developers
had a solid background in math and relational theory.
Sadly, that's not the case anymore...

select * from a,b where a.id=b.id

Suggests a Cartesian product between two relations then a filter to keep
only matching rows.

That's a join. And that syntax is a *perfect* representation of it.

So to whom is ansi more clear? To the person who knows nothing about
databases and decided one day to get a certification and call themselves
an expert?

Or maybe the person who decided one day on a whim to get into databases
and not realize that tons of code from the prior decade use the old
style syntax?

> Because others are likely to read your query many more times than you
> write it, clarity of intent *is* important.

I've never worked in a place that used ANSI only syntax and I've never
had a problem with clarity nor any developers I've worked with.
So, I don't at all get what you're saying...

Old style is short and sweet and perfect.
Ansi dumbed it down, that's the bottom line.
And for people who've been developing for sometime,
It's wholly unnecessary.

Regards,
Anthony

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Daryl Richter
Sent: Tuesday, September 27, 2005 9:24 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] how to do 'deep queries'?

Anthony Molinaro wrote:
> that query is 100% correct.
>
> it's just an equijoin (a type of inner join) between 3 tables.
>
> the syntax you show is how queries should be written and is more
> representative of what a joins between relations really are:
> Cartesian products with filters applied
>
> the ansi syntax, the explicit JOIN ... ON stuff is (imho)
unnecessary,
> useful only for outer joins since all the vendors did it differently.
>

Whether you feel that is unnecessary or not, it *is* the ANSI Standard
and is thus, by definition, "how queries should be written."

In addition to cleaning up the outer join issue, it was added to make
the *intention* of the query clearer.

Because others are likely to read your query many more times than you
write it, clarity of intent *is* important.

> what you have will work for postgreSQL, I used the syntax you show in
my
> book
> for every single join recipe except for outjoins.
>
> are you seeing errors?
>
> regards,
> Anthony

[original snipped]

--
Daryl

"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Axel Rau 2005-09-27 14:44:27 Re: Updating cidr column with network operator
Previous Message Daryl Richter 2005-09-27 14:02:16 Re: Updating cidr column with network operator