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.