The curious thing was that the SQL query produced the correct results in spite of the
Cartesian join! Of course performance was not optimal because a full table scan was required. Here's how it all went down.
Here is a query on the Employees and Departments tables: print the names of the employees in the IT department. There are individual restrictions on each table but there is no joining condition involving both tables.
SELECT e.department_id,
e.first_name,
e.last_name
FROM employees e,
departments d
WHERE e.department_id = 60
AND d.department_name = 'IT';
And here are the results. They are correct!
DEPARTMENT_ID FIRST_NAME LAST_NAME
------------- -------------------- -------------------------
60 Alexander Hunold
60 Bruce Ernst
60 David Austin
60 Valli Pataballa
60 Diana Lorentz
Here is the query plan. Notice the Cartesian join operation and the full scan of the Departments table.
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN CARTESIAN | |
| 2 | TABLE ACCESS FULL | DEPARTMENTS |
| 3 | BUFFER SORT | |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
| 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |
-----------------------------------------------------------
The reason why the query produces the correct results is that the restriction on the department_name column of the Department table produces exactly one row.
Here is the corrected query; the correct join condition has been added.
SELECT e.department_id,
e.first_name,
e.last_name
FROM employees e,
departments d
WHERE e.department_id = 60
AND d.department_name = 'IT'
AND e.department_id = d.department_id;
The results did not change but the query plan is now more conventional; the Cartesian join operation is no longer present.
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |
| 3 | INDEX UNIQUE SCAN | DEPT_ID_PK |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
| 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |
----------------------------------------------------------
You can try the above queries in the HR
sample schema if it is installed in your database.
