oracle join syntax (old vs new)
i'm an oracle developer and use the old join syntax, eg:
from emp, dept
where emp.deptno = dept.deptno;
I've noticed that mysql uses the newer (oracle 9i and up) method of joining tables, eg:
select ename, dname, emp.deptno, dept.deptno
from SCOTT.EMP inner join SCOTT.DEPT
on emp.deptno = dept.deptno
Does mysql support the old oracle method of joining tables? I've struggle to get my head around the newer INNER JOIN, FULL OUTER JOIN etc syntax
Yes and no.
MySQL (and *ALL* ANSI-compliant databases) accept the use of
That is, you *MUST* convert
CAUTION: One reason that *= went out of style is because of the problems associated with it. LEFT JOIN fixes those problems, so long as you take a reasonable modicum of care. Read this old post of mine (which is a copy of a post I made almost 10 years ago in another forum, a SQL Server forum, in fact):
thanks for the response. So presumably the (+) outerjoin syntax isnt supported either, eg:
Sorry, I used *= because that's what I remembered the old syntax to be. If it's += then that's what I should have used.
In any case none of those, other than just =, are supported any more. (And are deprecated even in Oracle, you know.)
The reason they don't work is that there is no way to handle the example that I showed: Where an outer join gets incorrectly converted to an inner join. That is, there is no way to distinguish between the conditions that create the join and the conditions outside of the join. Using the LEFT JOIN (or RIGHT JOIN, but that's just syntactic sugar...all RIGHT JOINs can easily be converted to LEFT JOINs) syntax, the ON keyword allows you to separate JOIN conditions from common WHERE conditions.
|All times are GMT +1. The time now is 11:12 AM.|
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.