JOIN TYPES - II
Cartesian Join or Cross Join
It joins every row of one table with every row of another table. Cartesian join is obtained when you give invalid join condition or omit join condition. To avoid Cartesian join a should have at least N-1 join condition.
Code:
Ex:- SELECT a.ename,a.job,a.sal , b.dname
FROM emp a , dept b;
ANSI SYNTAX
SELECT ename, job, sal, dname
FROM emp CROSS JOIN dept;
Natural Join
Natural join combines data from two tables based upon all common columns in both the table.
Code:
Ex:- SELECT ename, job, sal, dname
FROM emp NATURAL JOIN dept;
- Natural joins between two tables without at least one
common column results in Cartesian product.
- We can’t join more than two tables using this method.
- It may cause problems if columns are added or renamed
Self Join
Join the table with itself is called self join.
Code:
Ex:- SELECT a.ename, a.job, a.sal, b.ename Manager
FROM emp a, emp b
WHERE a.mgr = b.empno;
ANSI SYNTAX
SELECT a.ename, a.job, a.sal, b.ename Manager
FROM emp a JOIN emp b
ON a.mgr = b.empno;