Set operators are also called as vertical joins.
Set operators are used to combine two more result sets of queries.
Set operators are applicable only all queries having same number of attributes.
The attributes are compatible or having same datatype.
All set operators have equal precedence ,so sql contains one or more set operators then evaluates from left to right .
The set operators rerurns column names of first query.
Sql supports the 4 set operators.
·
Union
·
Union all
·
Minus
·
Intersect
SYNTEX: select * from tab1 {this is component query}
Union/union all/intersect/minus {set operator}
Select * from tab1; {this is component query}
Consider two tables
SQL> select * from emp;
EID
ENAME SAL DEPT
---------- -------------------- ---------- ----------
10
ramu 10000 ece
30
adams 50000 eee
20
raju 30000 cse
40
scott 60000 ece
40
king 50000 ece
SQL> select * from emp1;
EID
ENAME SAL DEPT
---------- -------------------- ---------- ----------
20
raju 30000 cse
40
scott 60000 ece
50
ramesh 20000 mca
1.Union: using union multiple queries can be put together and their output can be combined.
Union operator eliminates duplicate values those are having exact values in the both result sets.
EX: SQL>
select * from emp
2
union
3
select * from emp1;
EID ENAME SAL DEPT
----------
-------------------- ---------- ----------
10 ramu 10000 ece
20 raju 30000 cse
30 adams 50000 eee
40 king 50000 ece
40 scott 60000 ece
50 ramesh 20000 mca
6 rows
selected.
SQL>
select eid from emp
2
union
3
select eid from emp1;
EID
----------
10
20
30
40
50
SQL>
select ename,sal,dept from emp
2
union
3
select ename,sal,dept from emp1;
ENAME SAL DEPT
--------------------
---------- ----------
adams 50000 eee
king 50000 ece
raju 30000 cse
ramesh 20000 mca
ramu 10000 ece
scott 60000 ece
6 rows
selected.
2.Union all: same as union ,but does not eliminate duplicate values.
EX:
SQL>
select ename,sal,dept from emp
2
union all
3
select ename,sal,dept from emp1;
ENAME SAL DEPT
--------------------
---------- ----------
ramu 10000 ece
adams 50000 eee
raju 30000 cse
scott 60000 ece
king 50000 ece
raju 30000 cse
scott 60000 ece
ramesh 20000 mca
8 rows selected.
* Union returns only distinct rows that appear in either result.union all returns all rows.
* Union performs sorting and groupby to eliminate duplicates.but unionall doesn't perform sorting and groupby because unionall returns duplicates also.
* We know the data does not have duplicates in this we use Union all then performance will be increased than union.
3.Intersect: returns those rows which are retrieved by both queries.
SQL>
select ename,sal,dept from emp
2
intersect
3
select ename,sal,dept from emp1;
ENAME SAL DEPT
--------------------
---------- ----------
raju 30000 cse
scott 60000 ece
SQL>
select * from emp
2
intersect
3
select * from emp1;
EID ENAME SAL DEPT
----------
-------------------- ---------- ----------
20 raju 30000 cse
40 scott 60000 ece
4.Minus: returns all rows retrieved by the first query but not by the second query.
Ex:List employees who r in emp , but not in emp1
SQL>
select * from emp
2
minus
3
select * from emp1;
EID ENAME SAL DEPT
----------
-------------------- ---------- ----------
10 ramu 10000 ece
30 adams 50000 eee
40 king 50000 ece
Ex:List employees who r in emp1 , but not in emp
SQL>
select * from emp1
2
minus
3
select * from emp;
EID ENAME SAL DEPT
----------
-------------------- ---------- ----------
50
ramesh 20000 mca
No comments:
Post a Comment