MySQL statement using OUTER JOIN vs using WHERE to set conditions...

Question

For the statements with INNER JOIN:

SELECT column(s) FROM table1
INNER JOIN table2 ON condition(s)
...
INNER JOIN tableN ON condition(s);

I can write an equivalent statement with this:

SELECT column(s) FROM table1, table2, ..., tableN WHERE condition(s);

notice how I use WHERE to set my conditions in the second statement.

Question: can I write equivalent statements using WHERE to set my conditions for any OUTER (LEFT/RIGHT) JOIN statements as well?

Answer 1

I think:

SELECT ... FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON (t1.x = t2.y AND somecondition)

Answer 2

I am no MYSQL expert, but in oracle the syntax for an outer join in a where condition is where t1.id += t2.id

Answer 3

From the MySQL 5.0 Reference Manual :

"INNER JOIN and, (comma) are semantically equivalent in the absence of a join condition" So to me, that would read that a comma implies an INNER JOIN.

I recommend you use ANSI join syntax for greater clarity and portability.

Answer 4

can I write equivalent statements using WHERE to set my conditions for any OUTER (LEFT/RIGHT) JOIN statements as well?

No, not in ANSI SQL or MySQL. Some other databases have their own syntax that was used before the ANSI JOIN syntax was accepted. For example in Oracle 8:

WHERE table1.id=table2.thing (+)

But today the ANSI JOIN syntax should generally be preferred for both kinds of join.

Answer 5

In sybase you can use table1.field1 = table2.field1 (left outer) = right outer