Identifying and Avoiding Cartesian Products - SQL Server

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.

TIPS :

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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table