Set Operators in Oracle SQL
UNION
The Union Operator returns all rows that are selected by either query.
Use the UNION Operator to return all rows from multiple tables and eliminate
any duplicate rows.
The No.f columns and data types of the cols being selected must be identical in all the
SELECT statements used in the query.The name of the cols need not be identical.
UNION operates over all the cols selected.
NULL values are not ignored during duplicate checking.
By default the output is sorted in ascending order of the first column of the SELECT clause.
If records that occur in both the tables are identical the records are displayed only once.
Ex.
Two Tables
CREATE TABLE sales_2005(person_name VARCHAR2(40),amount NUMBER)
/
CREATE TABLE sales_2006(person_name VARCHAR2(40),amount NUMBER)
/
INSERT INTO sales_2005 VALUES(‘Joe’,1000)
/
INSERT INTO sales_2005 VALUES(‘Alex’,2000)
/
INSERT INTO sales_2005 VALUES(‘Bob’,5000)
/
INSERT INTO sales_2005 VALUES(‘Joe’,2000)
/
INSERT INTO sales_2006 VALUES(‘Alex’,2000)
/
INSERT INTO SALES_2006 VALUES(‘Zach’,35000)
/
SELECT * FROM sales_2005
/
SELECT * FROM sales_2006
/
Using UNION Operator
SELECT person_name,amount
FROM sales_2005
UNION
SELECT person_name,amount
FROM sales_2006
/
SELECT person_name person,amount
FROM sales_2005
UNION
SELECT person_name,amount
FROM sales_2006
/
SELECT person_name,amount
FROM sales_2005
UNION
SELECT person_name,amount
FROM sales_2006
ORDER BY 2 DESC
/
SELECT person_name,amount
FROM sales_2005
ORDER BY 2 DESC
UNION
SELECT person_name,amount
FROM sales_2006
/
This SQL Statement will error out.
ORDER BY comes at the last of the SQL statement
UNION ALL
The UNION ALL Operator returns results from both queries including duplications.
The UNION ALL Operator does not eliminate duplicate rows.
UNION returns all distinct rows selected by either query but UNION ALL returns all
rows selected by either query including all duplicates.
Using UNION ALL Operator
SELECT PERSON_NAME,AMOUNT
FROM SALES_2005
UNION ALL
SELECT PERSON_NAME,AMOUNT
FROM SALES_2006
/
INTERSECT Operator
The Intersect Operator returns rows that are common to both queries.
This takes the results of two queries and returns only rows that appear in both result sets.
The INTERSECT returns only the records that have the same values in the selected columns in both the tables.
Using INTERSECT Operator
SELECT PERSON_NAME,AMOUNT
FROM SALES_2005
INTERSECT
SELECT PERSON_NAME,AMOUNT
FROM SALES_2006
/
SELECT DEPTNO
FROM EMP
INTERSECT
SELECT DEPTNO
FROM DEPT
/
MINUS Operator
The MINUS Operator returns rows in the first query that are not present in the second query.
Use the MINUS Operator to return rows returned by the first query that are not present in the second query
Minus returns only unique rows returned by the first query but not by the second:
SELECT PERSON_NAME,AMOUNT
FROM SALES_2005
MINUS
SELECT PERSON_NAME,AMOUNT
FROM SALES_2006
/
SELECT PERSON_NAME,AMOUNT
FROM SALES_2006
MINUS
SELECT PERSON_NAME,AMOUNT
FROM SALES_2005
/
SELECT DEPTNO
FROM DEPT
MINUS
SELECT DEPTNO
FROM EMP
/
SELECT PERSON_NAME,AMOUNT
FROM SALES_2006
MINUS
SELECT PERSON_NAME,AMOUNT
FROM SALES_2005
/
Set Operator Guidelines
1.The Expressions in the SELECT List must match in number and datatype.
2.Paranthesis can be used to alter the sequence of execution
3.The ORDER BY Clause
Can appear only at the end of the statement
4.Duplicate rows are automatically eliminated except in UNION ALL Operator.
5.Column Names from the first query appear in the result.
6.The Output is sorted in ascending order by default except in UNION ALL.
Matching the SELECT Statements
SELECT PERSON_NAME,NULL
FROM SALES_2005
UNION
SELECT NULL,AMOUNT
FROM SALES_2006
/
SELECT PERSON_NAME,0
FROM SALES_2005
UNION
SELECT NULL,AMOUNT
FROM SALES_2006
/
SELECT ‘sing’ AS “My Dream”, 3 “Dummy”
FROM DUAL
UNION
SELECT ‘I”d like to teach’,1 “Dummy”
FROM DUAL
UNION
SELECT ‘the World to’,2 “Dummy”
FROM DUAL
/
— Query to produce an English sentence using two UNION Operators —
SELECT ‘sing’ AS “My Dream”, 3 “Dummy”
FROM DUAL
UNION
SELECT ‘I”d like to teach’,1 “Dummy”
FROM DUAL
UNION
SELECT ‘the World to’,2 “Dummy”
FROM DUAL
order by 2
/