1. INTERSECT clause : As the name suggests, the intersect clause is used to provide the result of the intersection of two select statements. This implies the result contains all the rows which are common to both the SELECT statements. Syntax :
SELECT column-1, column-2 …… FROM table 1 WHERE….. INTERSECT SELECT column-1, column-2 …… FROM table 2 WHERE…..
Example : Table 1 containing Employee Details
SELECT ID, Name, Bonus FROM table1 LEFT JOIN table2 ON table1.ID = table2.Employee_ID INTERSECT SELECT ID, Name, Bonus FROM table1 RIGHT JOIN table2 ON table1.ID = table2.Employee_ID;
Result :
SELECT column-1, column-2 …… FROM table 1 WHERE….. EXCEPT SELECT column-1, column-2 …… FROM table 2 WHERE…..
Example : Table 1 containing Employee Details
SELECT ID, Name, Bonus FROM table1 LEFT JOIN table2 ON table1.ID = table2.Employee_ID EXCEPT SELECT ID, Name, Bonus FROM table1 RIGHT JOIN table2 ON table1.ID = table2.Employee_ID;
Result :