Bad JOINs, what you gonna do

merlyn on 2004-03-05T18:50:14

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.

---

JOIN and optimizations

iburrell on 2004-03-05T23:47:58

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 much clearer about what is going on.

easier to learn SQL this way?

amackey on 2004-03-23T14:26:54

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 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.

Re:easier to learn SQL this way?

amackey on 2004-03-25T04:33:13

Hmm, actually it looks like Oracle 9i has SQL92 support, so nevermind that.