Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

merlyn (47)

merlyn
  merlyn@stonehenge.com
http://www.stonehenge.com/merlyn/
AOL IM: realmerlyn (Add Buddy, Send Message)
Yahoo! ID: realmerlyn (Add User, Send Message)

PAUSE-ID: MERLYN [cpan.org].
See my home page [stonehenge.com].

Journal of merlyn (47)

Friday March 05, 2004
01:50 PM

Bad JOINs, what you gonna do

[ #17773 ]
I was consulting with someone who had written

SELECT ...
FROM a, b, c
WHERE $some_condition_a OR $some_other_condition_b
AND a.foo = b.foo
AND b.bar = c.bar

The problem is that the joining expressions at the end were and'ed only up to the OR, because OR is lower precedence. So, he was getting a full outer join (full cross product) of the three large tables, and futzing memory in the process.

What he should have written was:

SELECT ...
FROM a, b, c
WHERE ($some_condition_a OR $some_other_condition_b)
AND a.foo = b.foo
AND b.bar = c.bar

so that the OR doesn't beat up the AND.

But then I got to thinking... with Real Databases (like PostgreSQL and Oracle) that speak full SQL92, you can always specify the join conditions for the tables up in the SELECT clause:

SELECT ...
FROM a NATURAL JOIN b NATURAL JOIN c
WHERE ($some_condition_a OR $some_other_condition_b)

and now we've clearly separated the expressions required to hook the tables together from the expressions selecting our items of interest. (In fact, the parens are no longer required there.)

Through a combination of NATURAL JOIN, JOIN USING (columns), and JOIN ON (condition), we can always do this. I'm making a resolution to myself to never write a join condition in the WHERE clause, ever again.

And yes, although MySQL halfway supports this (only in some kinds of joins), it also discourages their use. I wonder why.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • There are two reasons I have encountered for implicit joins. Some systems don't support the synrax. SQL::Statement is the one I have encountered.

    Also, some databases don't optimize as well with explicit joins. Until recently, PostgreSQL would not reorder joins and this limited how well the optimizer worked. The most recent version of PostgreSQL fixed this problem. But OUTER JOINs can't be reordered without potentially changing the results.

    I much prefer explicit joins. I think they the queries mu

  • This is actually how I teach SQL, insisting on the explicit syntax for clarity (and sometimes correctness, as you point out in your example). See sql_tutorial.pdf [virginia.edu] for the material I use. I absolutely forbid the use of commas as join operators.

    Unfortunately, Oracle has no support for standard SQL, using it's own odd syntax for joins (comma-separated tables, and where clauses with optional "+" symbols to denote outer join directions). Ugly.