SET Operations in SQL

Posted on by

There are four types of set operators

  1. UNION
  2. UNION ALL
  3. INTERSECT
  4. 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.