Set Theory of JOINS

Those who are not familiar with the JOINS concept.Let's go back to the school.We had SET Theory in Mathematics.You might have heard like UNION,INTERSECT,MINUS.If you digest these things,JOINS is not a matter,but a syntax.

Let's Consider two sets,
A={1,2,3,4,5}  B={1,3,5,7}

A UNION B ={1,2,3,4,5,7}  --Just clubs all the elements without duplicates.

A UNION ALL B={1,1,2,3,3,4,5,5,7} ---Same as UNION,but with duplicates

A INTERSECT B={1,3,5}   ----figures out only the common values in the sets.

A MINUS B={2,4}  -- figures out the values which are common in A and B but not common in A.

B MINUS A={7}  -- figures out the values which are common in A and B but not common in B.

Here,we have deptno as the common column between EMP and DEPT tables.Also,observe the distinct department numbers that the
tables have.
EMP table has {10,20,30,50}
DEPT table has {10,20,30,40}

If we apply the same mathematics here,then it really works.

UNION operator

SELECT DEPTNO FROM EMP
UNION
SELECT DEPTNO FROM DEPT;

Output:
10,20,30,40,50

UNION ALL operator

SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM DEPT;

Output:Unlike UNION,Combines all the departments without eliminating the duplicates.

INTERSECT operator
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;

Output:10,20,30



MINUS operator
SELECT DEPTNO FROM EMP
MINUS
SELECT DEPTNO FROM DEPT;
Output:50

SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;
Output:40


Example:If you want to get the all the details of employees who has common departments between the tables.

INTERSECT vs INNER JOIN

SELECT * FROM EMP WHERE DEPTNO
IN
(
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT
);

This is equivalent to INNER JOIN

SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPTNO.DEPTNO;

INNER JOIN plays the role of INTERSECT


Like wise,

A LEFT OUTER JOIN B means

A INTERSECT B  UNION  A MINUS B