SET Operators in Oracle with Examples
QLsetoperatorsallowcombiningresultsfromtwoormoreSELECTstatements.Atfirstsight,thislookssimilarto
S
SQL joins although thereisabigdifference.SQLjoinstendstocombinecolumnsi.e.witheachadditionallyjoined
table it is possible to select more and more columns. SQL set operators on the other hand combine rows from
different querieswithstrongpreconditions–allinvolvedSELECTSmust.Joinswearecollectingthedatafromtwo
tables when there is common data. But in set operators the data is not joined, in this, the data is merged
1. Retrieve the same number of columns and
2. ThedatatypesofcorrespondingcolumnsineachinvolvedSELECTmustbecompatible(eitherthesameor
with possibility implicitly convert to the data types of the first SELECT statement).
Types of SET Operators in Oracle:
There are four types of SET Operators available in Oracle. They are as follows:
1. UNION: It Returns all distinct rows selected by either query
2. UNION ALL:It Returns all rows selected by either query, including all duplicates
3. INTERSECT: It Returns all distinct rows selected by both queries
4. MINUS: It Returns all distinct rows selected by the first query but not the second
ou can combine multiple queries using the set operators UNION, UNIONALL,INTERSECT,andMINUS.Allset
Y
operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database
evaluates them from the left to right unless parentheses explicitly specify another order.
The corresponding expressions in the select lists of the component queries of a compound query mustmatchin
number and must be in the same datatype group.
If component queries select character data, then the data type of the return values are determined as follows:
1. If both queries select values of datatype CHAR of equal length, then the returned valueshavedatatype
CHARofthatlength.IfthequeriesselectvaluesofCHARwithdifferentlengths,thenthereturnedvalueis
VARCHAR2 with the length of the larger CHAR value.
2. IfeitherorbothofthequeriesselectvaluesofdatatypeVARCHAR2,thenthereturnedvalueshavedatatype
VARCHAR2.
Inqueriesusingsetoperators,Oracledoesnotperformimplicitconversionacrossdatatypegroups.Therefore,ifthe
correspondingexpressionsofcomponentqueriesresolvetobothcharacterdataandnumericdata,Oraclereturnsan
error.
Set Operator Guidelines in Oracle:
1. Ineveryresultsetthedatatypeofeachcolumnmustbecompatible(well-matched)tothedatatypeofits
orresponding column in other result sets.
c
2. The result sets of all queries must have the same number of columns.
3. Parentheses can be used to alter the sequence of execution.
. In order to sort the result, an ORDERBYclauseshouldbepartofthelastselectstatement.Thecolumn
4
names or aliases must be found out by the first select statement, or the positional notation
5. Column names from the first query appear in the result.
Advantage of SET operators in Oracle:
1 . se a set operator to combine multiple queries into a single query
U
2. These operators are used to combine the information of similar data types from one or more than one table.
Restrictions on the Set Operators:
The set operators are subject to the following restrictions:
1. The ORDER BY clause doesn’t recognize the column names of the second SELECT
2. The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.
3. The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.
4. Set operations are not allowed on SELECT statements containing TABLE collection expressions.
5. SELECT statements involved in set operations can’t use the FOR UPDATE clause.
SQLstatementscontainingthesesetoperatorsarereferredtoascompoundqueries,andeachSELECTstatementin
acompoundqueryisreferredtoasacomponentquery.TwoSELECTscanbecombinedintoacompoundquerybya
set operation only if they satisfy the following two conditions:
1. The result sets of both the queries must have the same number of columns.
2. Thedatatypeofeachcolumninthesecondresultsetmustmatchthedatatypeofitscorrespondingcolumn
in the first result set.
Syntax of SET Operators in Oracle:
he generic syntax of a query involving a set operation is:
T
<Component Query>
{UNION | UNION ALL | MINUS | INTERSECT}
<Component Query>
Examples to understand SET Operators in Oracle:
e are going to use the following EmployeeUK and EmployeeUSA tables to understand the SET Operators in
W
Oracle.
Please use the below SQL Script to create the EmployeeUK and EmployeeUSA tables with the required data.
REATETABLEEmployeeUK
C
(
EmployeeIdINT,
FirstNameVARCHAR(20),
LastNameVARCHAR(20),
GenderVARCHAR(1 0),
DepartmentVARCHAR(2 0)
);
INSERTINTOEmployeeUKVALUES(1,'Pranaya','Rout','Male','IT');
INSERTINTOEmployeeUKVALUES(2,'Priyanka','Dewangan','Female','IT');
INSERTINTOEmployeeUKVALUES(3,'Preety','Tiwary','Female','HR');
INSERTINTOEmployeeUKVALUES(4,'Subrat','Sahoo','Male','HR');
INSERTINTOEmployeeUKVALUES(5,'Anurag','Mohanty','Male','IT');
INSERTINTOEmployeeUKVALUES(6,'Rajesh','Pradhan','Male','HR');
INSERTINTOEmployeeUKVALUES(7,'Hina','Sharma','Female','IT');
CREATETABLEEmployeeUSA
(
EmployeeIdINT,
FirstNameVARCHAR(20),
LastNameVARCHAR(20),
GenderVARCHAR(1 0),
DepartmentVARCHAR(2 0)
);
INSERTINTOEmployeeUSAVALUES(1 ,'James','Pattrick','Male','IT');
INSERTINTOEmployeeUSAVALUES(2 ,'Priyanka','Dewangan','Female','IT');
INSERTINTOEmployeeUSAVALUES(3 ,'Sara','Taylor','Female','HR');
INSERTINTOEmployeeUSAVALUES(4 ,'Subrat','Sahoo','Male','HR');
INSERTINTOEmployeeUSAVALUES(5 ,'Sushanta','Jena','Male','HR');
INSERTINTOEmployeeUSAVALUES(6 ,'Mahesh','Sindhey','Female','HR');
INSERTINTOEmployeeUSAVALUES(7
,'Hina','Sharma','Female','IT');
UNION Operator in Oracle
heUNIONoperatorisusedtocombinetheresultsetoftwoormoreSELECTstatementsintoasingleresultsetand
T
theneliminatesanyduplicaterowsfromthefinalresultset.ThatmeanstheUNIONOperatorselectsonlythedistinct
values.
Following is the Syntax to use UNION Operator in Oracle.
UNION Operator Example in Oracle:
he following SQL query combines two select statements using the UNION operator. In our example, both the
T
EmployeeUK and EmployeeUSA tables have seven records.
SELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUK
NION
U
SELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUSA;
he above statement combines the results of two queries with the UNION operator, which eliminates duplicate
T
selectedrows.Onceyouexecutetheabovequery,youwillgetthefollowingresultset.Pleaseobserve,herewedon’t
haveanyduplicatedata.Here,intheresultset,wegotatotalof11rowsoutof14rows.Thisisbecause3rowsare
present in both the result set.
UNION ALL Operator in Oracle
he UNIONALLoperatorisusedtocombinetheresultsetoftwoormoreSELECTstatementsintoasingleresult
T
including the duplicate values. Following is the pictorial representation of UNION ALL Operator.
Following is the Syntax to use UNION ALL Operator in Oracle.
UNION ALL Operator Example in Oracle:
hefollowingquerycombinestheresultsetsoftwoselectstatementsintoasingleresultsetusingtheUNIONALL
T
operator in Oracle.
ELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUK
S
UNIONALL
SELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUSA;
nceyouexecutetheaboveUNIONALLquery,youwillgetthefollowingresultset.Pleaseobserve,herewegotall
O
the 14 rows in the result set.
Differences between UNION and UNION ALL Operator in Oracle
heUNIONoperatorreturnsonlydistinctrowsthatappearineitherresult,whiletheUNIONALLoperatorreturnsall
T
rows.TheUNIONALLoperatordoesnoteliminateduplicateselectedrows. WhenweuseaUNIONoperator,inorder
to removetheduplicaterowsfromtheresultset,ithastodoadistinctoperationwhichistime-consuming.Forthis
reason, UNION ALL is much faster than UNION Operator in Oracle.
UNION/UNION ALL with ORDER BY Clause in Oracle
he UNION/UNION ALL Operator can be used with the ORDER BY clause to sort the result returned from the
T
query. SupposewewanttosorttheemployeesbyFirstNamecolumnvalues.ORDERBYclauseshouldbepartof
the last select statement. The SQL statement will be:
ELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUK
S
UNION
SELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUSA
ORDER BYFirstName;
owwhenyouexecutetheabovequeryandyouwillgetthefollowingoutput.Hereyoucanseetheemployeesare
N
sorted according to their FirstName column values.
INTERSECT Operator in Oracle
he INTERSECT operatorinOracleisusedtocombinetworesultsetsandreturnsthedatawhicharecommonin
T
boththeresultset.ThatmeanstheINTERSECTOperatorreturnsonlythoserowsthatarecommoninboththeresult
sets. Following is the pictorial representation of INTERSECT Operator.
Following is the syntax of INTERSECT operator in Oracle.
INTERSECT operator Example in Oracle:
hefollowingquerycombinestheresultsetsoftwoselectstatementsintoasingleresultsetusingtheINTERSECT
T
operator in Oracle.
ELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUK
S
INTERSECT
SELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUSA;
heabovestatementcombinestheresultswiththeINTERSECToperator,whichreturnsonlythoserowsreturnedby
T
bothqueries.OnceyouexecutetheaboveINTERSECTquery,youwillgetthefollowingresultset.Pleaseobserve,
here we got only 3 rows in the result set which are common in both the result set.
MINUS Operator in Oracle
heMINUSoperatorinOracleisusedtoreturnuniquerowsfromtheleftquerywhichisn’tpresentintherightquery’s
T
results. That means theMINUSOperatortakestheresultsetofthefirstselectstatementandremovesthoserows
that are returned by a second select statement. Following is the pictorial representation of the MINUS Operator.
Following is the syntax of the MINUS operator in Oracle.
MINUS Operator Example in Oracle:
he following SQL Query will return the unique rows from the left query (the select statementbeforetheMINUS
T
operator) that is not present in the right query (the select statement after the MINUS operator).
ELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUK
S
MINUS
SELECTFirstName, LastName, Gender, DepartmentFROMEmployeeUSA;
heabovestatementcombinesresultswiththeMINUSoperator,whichreturnsonlyuniquerowsreturnedbythefirst
T
query but not bythesecond.OnceyouexecutetheaboveMINUSOperatorquery,youwillgetthefollowingresult
set.Pleaseobserve,herewegotonly4rowsintheresultsetwhicharepresentinthefirstresultsetbutnotinthe
second result set.
In the next article, I am going to discuss U
NIONO
perator in OraclewithExamples.Here,inthisarticle,Itryto
explain SETO peratorsinOraclewithExamplesandIhopeyouenjoythisSETOperatorsinOraclewithExamples
article.IfyouhaveanyqueriesregardingtheOracleSETOperators,thenpleaseletusknowbyputtingyourqueryin
the comment section.
