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;
COPYRIGHT (C) 2001-2010 sqlparser.com , privacy policy ALL RIGHT RESERVED
|
