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
EMP_COMPANY TABLE
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
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 exceptWe 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 |
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
Do write some lees geeky posts also :-)
ReplyDeleteSure Tarun
ReplyDelete