Using Oracle Set Operators(Union,Union All,Intersect and Minus/Except)
Oracle Set operators can be used to select data from multiple tables. They combine the results of two or more queries. When using the Set operators
a) Each SELECT clause must have the same number of column
b) Column must be of same data-type
c) Column should be specified in the same order in all the select clauses.
There are 4 Set operators:
| UNION | It returns all unique rows returned from both queries |
| UNION ALL | It returns all rows including duplicates |
| INTERSECT | It returns only the rows returned from both queries |
| MINUS | It returns unique rows selected by first query but not the rows selected from second query |
Oracle Union Operator :
Oracle Union operator is used to combine the result sets of two or more SELECT statements. It combines the both SELECT statement result set and removes duplicate rows between them.So it basically returns the distinct rows
UNION
SELECT city_name FROM SUPP_DATA;
This query will return distinct rows of cities from “Cust_data” and “Supp_data”
Oracle Union All
Oracle Union All operator is used to combine the result sets of two or more SELECT statements. It combines the both SELECT statement result sets and return as it is. So it may contain duplicate also depending on the data-set
SELECT city_name FROM CUST_DATA
UNION All
SELECT city_name FROM SUPP_DATA
;
This query will return all rows(Duplicate rows also) of cities from “Cust_data” and “Supp_data”
Difference between Union and Union All in Oracle
Union returns the distinct rows while Union all returns all the rows. So we have to very careful while choosing these set operators
Oracle Intersect
Oracle Intersect operator is used to combine the result sets of two or more SELECT statements. It combines the both SELECT statement data-sets and return the distinct common rows between the statements. So If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
The results are shown by the shaded portion in the above figure
SELECT city_name FROM CUST_DATA
INTERSECT
SELECT city_name FROM SUPP_DATA
;
This query will return common city name from “Cust_data” and “Supp_data”
Difference between Intersect Vs Join
1) INTERSECT operator is used to retrieve the common records from both the select clause of the Intersect Operator Whereas Join is used to reterive data from two tables based on specified condition
2) INTERSECT does the comparison for all columns while INNER JOIN only the specified columns.
3) INTERSECT operator returns almost same results as INNER JOIN clause many times.
Oracle Minus
Sql minus operator is used to combine the result sets of two or more SELECT statements. It combines the both SELECT statement and return the rows which is selected from first select statement and then remove from the data-set all the rows retrieved from second select statement
The results are shown by the shaded portion in the above figure
SELECT city_name FROM CUST_DATA
MINUS
SELECT city_name FROM SUPP_DATA
;
This query will return city name from “Cust_data” minus the city name “Supp_data”
Here since all the data returned from select statement is same,it did not generate any rows.
Lets insert one different row in cust_data and see the results
Now lets see if we do minus of cust_data from supp_data
SELECT city_name FROM SUPP_DATA
MINUS
SELECT city_name FROM CUST_DATA
;
We dont minus operator in Many RDBMS like sql server ,mysql. There we use except in place of minus and it has same functionality as minus
These all sql set operators are valid for RDBMS used in Market like Oracle,mysql, sql server, Postgres, Teradata
Related Articles
delete duplicate records in oracle : Tips and ways on How to delete duplicate rows from oracle table . Various options are given to delete the duplicate rows without any issues
Oracle Sql tutorials : Contains the list of all the useful Oracle sql articles. Explore them to learn about Oracle Sql even if you know Oracle Sql
Oracle interview questions : Check out this page for Top 49 Oracle Interview questions and answers : Basics , Oracle SQL to help you in interviews.Additional material is also provided
where clause in oracle : Restricting the data set, where clause, what is where clause in sql statement, comparison operator’s
single row functions in Oracle : Check out this to find out Single row functions in sql,Oracle data functions,Numeric functions in sql ,Character function in sql