Oracle Set Operators: Union,Union All,Intersect and Minus

Last updated on September 14th, 2019 at 12:01 pm

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

oracle  union

SELECT city_name FROM CUST_DATA
UNION
SELECT city_name FROM SUPP_DATA;

This query will return distinct rows  of cities from “Cust_data” and “Supp_data”
oracle union example

 

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”

oracle union all example

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.

oracle  intersect

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”

sql intersect example

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

oracle minus

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”

oracle minus example

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

oracle union,union all, intersect minus set operators

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

 

Leave a Reply