General SQL Parser: sql engine for various databases

Rewrite Oracle Propriety Joins to SQL99 Compliant Joins

The Oracle9i database offers join syntax that is SQL99 compliant. Prior to release 9i, The join syntax was different from the ANSI standards. So I created the following table for reference when I migrates SQL in Oracle to other databases. We also have a free tool that can do this translation automatically. you may download it here


Cartesian product
---------------------------------
Oracle propriety:
SELECT e.employee_id,
       e.last_name,
       e.department_id,
       d.department_id,
       d.location_id
FROM   employees e,
       departments d;

SQL99:
SELECT e.employee_id,
       e.last_name,
       e.department_id,
       d.department_id,
       d.location_id
FROM   employees e
       CROSS JOIN departments d;




Equijoins(also called inner joins)
---------------------------------
Oracle propriety:
SELECT e.employee_id,
       e.last_name,
       e.department_id,
       d.department_id,
       d.location_id
FROM   employees e,
       departments d
WHERE  e.department_id = d.department_id;

SQL99:
SELECT e.employee_id,
       e.last_name,
       e.department_id,
       d.department_id,
       d.location_id
FROM   employees e
       JOIN departments d
         ON e.department_id = d.department_id;



Equijoins with additional search conditions
---------------------------------
Oracle propriety:
SELECT e.employee_id,
       e.last_name,
       e.department_id,
       d.department_id,
       d.location_id
FROM   employees e,
       departments d
WHERE  e.department_id = d.department_id
       AND last_name = 'Matos';

SQL99:
SELECT e.employee_id,
       e.last_name,
       e.department_id,
       d.department_id,
       d.location_id
FROM   employees e
       JOIN departments d
         ON e.department_id = d.department_id
            AND last_name = 'Matos';


Nonequiljoins
---------------------------------
Oracle propriety:
SELECT e.ename,
       e.sal,
       s.grade
FROM   emp e,
       salgrade s
WHERE  e.sal BETWEEN s.losal
                     AND s.hisal;

SQL99:
SELECTname,
       e.sal,
       s.grade
FROM   emp e
       JOIN salgrade s
         ON e.sal BETWEEN s.losal
                          AND s.hisal;


left outer join
---------------------------------
Oracle propriety:
SELECT e.employee_id,
       e.last_name,
       e.department_id
FROM   employees e,
       departments d
WHERE  d.department_id (+)  = e.department_id;


SQL99:
SELECT e.employee_id,
       e.last_name,
       e.department_id
FROM   employees e
       LEFT OUTER JOIN departments d
                    ON e.department_id = d.department_id;



right outer join
---------------------------------
Oracle propriety:
SELECT e.employee_id,
       e.last_name,
       d.department_id
FROM   employees e,
       departments d
WHERE  d.department_id = e.department_id (+) ;


SQL99:
SELECT e.employee_id,
       e.last_name,
       d.department_id
FROM   employees e
       RIGHT OUTER JOIN departments d
                     ON e.department_id = d.department_id;


Join more than two tables
---------------------------------
Oracle propriety:
SELECT e.last_name,
       d.department_name,
       l.city
FROM   employees e,
       departments d,
       locations l
WHERE  e.department_id = d.department_id
       AND d.location_id = l.location_id;


SQL99:
SELECT e.last_name,
       d.department_name,
       l.city
FROM   employees e
       JOIN departments d
         ON e.department_id = d.department_id
       JOIN locations l
         ON d.location_id = l.location_id;