Recipe3.8.Identifying and Avoiding Cartesian Products | SQL Cookbook (Cookbooks (OReilly))

Recipe 3.8. Identifying and Avoiding Cartesian Products

Problem

You want to return the name of each employee in department 10 along with the location of the department. The following query is returning incorrect data:

  select e.ename, d.loc   from emp e, dept d  where e.deptno = 10 ENAME       LOC ----------  ------------- CLARK       NEW YORK CLARK       DALLAS CLARK       CHICAGO CLARK       BOSTON KING        NEW YORK KING        DALLAS KING        CHICAGO KING        BOSTON MILLER      NEW YORK MILLER      DALLAS MILLER      CHICAGO MILLER      BOSTON

The correct result set is the following:

 ENAME       LOC ----------  --------- CLARK       NEW YORK KING        NEW YORK MILLER      NEW YORK

Solution

Use a join between the tables in the FROM clause to return the correct result set:

 1 select e.ename, d.loc 2   from emp e, dept d 3  where e.deptno = 10 4    and d.deptno = e.deptno

Discussion

Looking at the data in the DEPT table:

  select * from dept     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON

You can see that department 10 is in New York, and thus you can know that returning employees with any location other than New York is incorrect. The number of rows returned by the incorrect query is the product of the cardinalities of the two tables in the FROM clause. In the original query, the filter on EMP for department 10 will result in three rows. Because there is no filter for DEPT, all four rows from DEPT are returned. Three multiplied by four is twelve, so the incorrect query returns twelve rows. Generally, to avoid a Cartesian product you would apply the n1 rule where n represents the number of tables in the FROM clause and n1 represents the minimum number of joins necessary to avoid a Cartesian product. Depending on what the keys and join columns in your tables are, you may very well need more than n1 joins, but n1 is a good place to start when writing queries.

When used properly, Cartesian products can be very useful. The recipe, , uses a Cartesian product and is used by many other queries. Common uses of Cartesian products include transposing or pivoting (and unpivoting) a result set, generating a sequence of values, and mimicking a loop.