Q : Write a SQL query to get the employee name, Salary, designation, department number, department name and location for all managers working in “SALES” department?

Ans:

SELECT Ename, Sal, Job, d.Deptno, Dname, Loc
  FROM Emp e, Dept d
 WHERE e.Deptno = d.Deptno
   AND e.Job = 'MANAGER' AND d.Dname = 'SALES';

ENAME             SAL JOB           DEPTNO DNAME          LOC
---------- ---------- --------- ---------- -------------- -------------
BLAKE            2850 MANAGER           30 SALES          CHICAGO

What happens if join condition is ignored.

--Example 1.1

SELECT Ename, Sal, Job, d.Deptno, Dname, Loc
  FROM Emp e, Dept d
 WHERE e.Job = 'MANAGER' AND d.Dname = 'SALES';

ENAME             SAL JOB           DEPTNO DNAME          LOC
---------- ---------- --------- ---------- -------------- -------------
BLAKE            2850 MANAGER           30 SALES          CHICAGO
CLARK            2450 MANAGER           30 SALES          CHICAGO
JONES            2975 MANAGER           30 SALES          CHICAGO

Is the above output correct? Let’s verify.

--Example 1.2

SELECT Ename, Sal, Job, Deptno
  FROM Emp
 WHERE Job = 'MANAGER';

ENAME             SAL JOB        DEPTNO
---------- ---------- --------- -------
BLAKE            2850 MANAGER        30
CLARK            2450 MANAGER        10
JONES            2975 MANAGER        20

If you see Example 1.2, “CLARK” works in 10th department and “JONES” works in 20th department. But Example 1.1, shows they are in department 30.

So understand, if join condition is not mentioned properly, then the result may be a mess giving improper values.

What is Cartesian Product?

If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. In a Cartesian product, Oracle combines each row of one table with each row of the other. In our example of Emp and Dept tables, Emp contains 14 rows and Dept contains 4 rows. So, when a Cartesian product happens between these two tables the result will be 14 * 4 = 56 rows.

--Example 1.3

SELECT Ename, Sal, Job, Dname, Loc, d.Deptno
  FROM Emp e, Dept d;

56 rows selected.

You can do everything in a Cartesian product query that you do in other queries. Such as you can apply filter condition(s), order by clause in a Cartesian product query.

SELECT Ename, Sal, Dname, Loc, Dept.Deptno
  FROM Emp, Dept
 WHERE Dname = 'SALES' AND Sal > 2000
 ORDER BY 3, 2, 1;

WHERE Dname = 'SALES' is not a JOIN condition because it doesn’t include two columns, each from different tables. Similarly, Sal > 2000 is also not a Join condition. DName = 'SALES' AND Sal > 2000 are two filter conditions.

A Join without a Join condition most of the time produces meaningless output. Cartesian products are dangerous while working with joins as they can produce undesired output. But there are circumstances where a Cartesian product is really necessary and helpful.

Which situation Cartesian product is useful?

Let us understand the importance of Cartesian product using one example.

Q : Write a query to get the number of rows in Emp table, Dept table and Salgrade table?

Ans:

SELECT e.emp_count, d.dept_count, s.salgrade_count
  FROM ( SELECT count(*) emp_count FROM Emp) e,
       ( SELECT count(*) dept_count FROM Dept) d,
       ( SELECT count(*) salgrade_count FROM Salgrade) s;

 EMP_COUNT DEPT_COUNT SALGRADE_COUNT
---------- ---------- --------------
	14	    4		   5

Cartesian products are useful where one table containing one row has to be joined with another table with one or more than one rows.

There is one more use case where a Cartesian product is appropriate and really essential. Cartesian product are used to generate a large amount of rows to use for testing. Cartesian product morphs data and makes it suitable for designing a test database for teams to conduct testing.