Rules for SQL programming, #48

Friday, August 28. 2009

I was just reading something that reminded me of a piece of advice I have often given people over the last few years: Don't mix use of the "," operator with use of JOIN operators in your FROM clause. It's a recipe for endless confusion and errors, in my experience.

I know of shops that go further and ban the use of the "," altogether, and require only explicit joins. I'm old-fashioned enough not to go that far - back when I was first learning SQL we didn't have fancy things like outer joins, and it was a decade later that DB2 (the system I first learned SQL on) acquired them, so I'm kinda used to using them.

But I have learned to my cost that mixing the old syntax with explicit JOIN syntax confuses me and lots of other people as well. So do one or the other, but don't mix them - that's my advice.

Posted by Andrew Dunstan in PostgreSQL at 01:57 | Comments (2) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

I'm a recent convert of explicit JOIN statements -- it's a much clearer separation from the WHERE clause conditions IMHO.<br />
<br />
But apart from the confusions, are there any performance issues? Correct me if I'm wrong, but the "," specifies a CROSS JOIN, but modern databases should be able to optimise it to use INNER JOINS given sufficient conditions in the WHERE clause right? Or are there exceptions?

#1 Sean Hsien on 2009-08-28 03:31 (Reply)

No, performance-wise they should work the same as the equivalent query written the other way, I believe.<br />
<br />
It's a matter of clarity rather than performance.

#1.1 Andrew Dunstan on 2009-08-28 03:54 (Reply)

Add Comment