SET Operations in SQL
There are four types of set operators
- UNION
- UNION ALL
- INTERSECT
- MINUS
UNION OPERATOR :
The union operator returns from both queries after eliminating duplicates
Query :
SELECT ename, job, deptno FROM emp WHERE sal BETWEEN 1500 and 3000
UNION
SELECT ename, job, deptno FROM emp WHERE deptno not in (10,30);
UNION ALL OPERATOR :
- The Union All operator returns result from both queries including all duplicates.
- The Union All executes faster than union after comparing union has the time to eliminate duplicate.
Query:
SELECT ename, job, deptno FROM emp WHERE sal BETWEEN 1500 and 3000
UNION ALL
SELECT ename, job, deptno FROM emp WHERE deptno not in (10,30);
INTERSECT OPERATOR:
The Intersect operator returns rows that are common to both queries.
Query :
SELECT ename, job, deptno FROM emp WHERE sal BETWEEN 1500 and 3000
INTERSECT
SELECT ename, job, deptno FROM emp WHERE deptno not in (10,30);
MINUS OPERATOR :
The Minus operator returns rows in the first query that are not present in the second query.
Query :
SELECT ename, job, deptno FROM emp WHERE sal BETWEEN 1500 and 3000
MINUS
SELECT ename, job, deptno FROM emp WHERE deptno not in (10,30);
SET Operators Guidelines:
- The Expression in the SELECT list must match in number and datatype.
- Parenthesis can be used to alter the sequence of execution.
- The Order By Clause :
- can appear only at the very end of the statement.
- will accept the column name, column alias from the first SELECT statement or positional notation.