Home > Oracle, SQL > The Curious Case of The Cartesian Join

The Curious Case of The Cartesian Join


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. 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.

Categories: Oracle, SQL
  1. Anthony
    August 5, 2009 at 8:20 pm

    I can’t tell you how many times I’ve looked into database responsiveness issues, or just general peculiar behavior type bugs, only to find out the sql was written by someone with no clue as to how to properly write it.

  2. Iggy Fernandez
    August 5, 2009 at 9:37 pm

    We are taught SQL syntax without reference to the underlying theory of relational algebra and relational calculus. We are also told that efficiency is the query optimizer’s problem, not our problem.

    My favorite story is about a developer who submitted an SQL query that joined seven large tables but did not include any joining conditions whatsoever. He then opened a high-priority ticket demanding that the database administrators find out why Oracle was not responding to “simple” queries. V$SQLPLAN showed that the estimated cost of the query was about ten thousand trillion, give or take a few🙂 We asked him why he had not included any joining conditions. He said that he wanted to make sure that Oracle could process “simple” queries before he submitted any “complex” queries🙂

    ANSI join syntax can help avoid inadvertent Cartesian joins but old habits die hard. Here is how I could have written the above query.


    SELECT
      e.department_id,
      e.first_name,
      e.last_name
    FROM
      employees e
      INNER JOIN departments d ON (e.department_id = d.department_id)
    WHERE e.department_id = 60;

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: