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.
EMPNOENAMEJOBMGRDEPTNOCITY
7369ANILCLERK 790220 NAGPUR
7521SHANKARSALESMAN 769830 BOMBAY
7566SUNILMANAGER 7839 20 BOMBAY
7369ANILCLERK 769830MADRAS
7654JAYACLERK 790220 NAGPUR
7698VIJAYMANAGER 790220 DELHI
7782PRAKASHMANAGER 790220 CALCUTTA
7788AJAYANALYST 790220 MADRAS
7839KINGPRESIDENT 790220 CALCUTTA
7844TURNERSALESMAN 790220 BOMBAY
7876ADAMSCLERK 790220 DELHI
7900JAMESCLERK 790220 DELHI
7902FORDANALYST 790220 CALCUTTA
7934MILLERCLERK 790220 CALCUTTA
EMP_COMPANY TABLE
ENAMECNAMESALARYJDATE
ANILACC 15001-May-89
SHANKAR TATA 200010-Jul-90
JAYA CMC 1800 7-Jun-91
SUNIL CMC1700 1-Jan-88
VIJAYTATA 5000 3-Jan-88
PRAKASH TATA 3000 27-May-89
AJAY ACC8000 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

Comments

Post a Comment

Popular posts from this blog

Hadoop - Hive - Load data from csv/xls files

Microsoft BI Implementation - Cube back up and restore using XMLA command