Oracle Set Operators

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

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