Oracle V MS-SQL - Set operators

Sql set operations allows us to combine the results from multiple query into one. It is purely based on the mathematical set theory. Each sql statement can be treated as one set and we can combine this result by the operator. We should make note of the point that if you want to use these operators there should be equal number of columns selected in all the queries and also data type of each column should match with the same column of other queries.

In case of oracle same data type in all the queries are not required since it will implicitly convert the data type but not in all the cases. For e.g handling null.

Select 1 num, a name from dual
Union
Select null, ‘jithesh’ name from dual;

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

Operator     ORACLE       MS-SQL

UNION        Available    Available
UNION ALL    Available    Available
INTERSECT    Available    Available
MINUS        Available    except

We will use the below mentioned tables to illustrate the operators.

EMPNO
ENAME
JOB
MGR
DEPTNO
CITY
7369
ANIL
CLERK 
7902
20 
NAGPUR 
7521
SHANKAR
SALESMAN 
7698
30 
BOMBAY
7566
SUNIL
MANAGER 
7839 
20 
BOMBAY 
7369
ANIL
CLERK 
7698
30
MADRAS
7654
JAYA
CLERK 
7902
20 
NAGPUR 
7698
VIJAY
MANAGER 
7902
20 
DELHI 
7782
PRAKASH
MANAGER 
7902
20 
CALCUTTA 
7788
AJAY
ANALYST 
7902
20 
MADRAS 
7839
KING
PRESIDENT 
7902
20 
CALCUTTA 
7844
TURNER
SALESMAN 
7902
20 
BOMBAY 
7876
ADAMS
CLERK 
7902
20 
DELHI 
7900
JAMES
CLERK 
7902
20 
DELHI 
7902
FORD
ANALYST 
7902
20 
CALCUTTA 
7934
MILLER
CLERK 
7902
20 
CALCUTTA 

EMP_COMPANY TABLE


ENAME
CNAME
SALARY
JDATE
ANIL
ACC 
1500
1-May-89
SHANKAR 
TATA 
2000
10-Jul-90
JAYA 
CMC 
1800 
7-Jun-91
SUNIL 
CMC
1700 
1-Jan-88
VIJAY
TATA 
5000 
3-Jan-88
PRAKASH 
TATA 
3000 
27-May-89
AJAY 
ACC
8000 
30-Apr-95
AMOL 
ACC 
1000 
17-Mar-95

Union

Combines the results of two SELECT statements into one result set.

SELECT ename FROM emp (see the table)
UNION
SELECT ename FROM emp_company (see the table)

Result
ADAMS
AJAY
AMOL
ANIL
FORD
JAMES
JAYA
KING
MILLER
PRAKASH
SHANKAR
SUNIL
TURNER
VIJAY

Union All

It combines the result of multiple query and gives all the possible values

SELECT ename FROM emp
UNION ALL
SELECT ename FROM emp_company

Result
ADAMS
AJAY
ANIL
FORD
JAMES
JAYA
KING
MILLER
PRAKASH
SHANKAR
SUNIL
TURNER
VIJAY
AJAY
AMOL
ANIL
JAYA
PRAKASH
SHANKAR
SUNIL
VIJAY

Minus

This operator takes all the details from first query which are not there in the second query ie. Take un common data from the first query result.

SELECT ename FROM emp (see the table)
MINUS
SELECT ename FROM emp_company(see the table)

Result
ADAMS
FORD
JAMES
KING
MILLER
TURNER


Intersect


Returns only those rows that are returned by each of two SELECT statements ie, take common data.

SELECT DISTINCT ename FROM emp
INTERSECT
SELECT DISTINCT ename FROM EMP_COMANY

Result

AJAY
ANIL
JAYA
PRAKASH
SHANKAR
SUNIL
VIJAY