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

Using Sql Set Operators(Union,Union All,Intersect and Minus/Except)

Sql 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

 

Sql Union Operator :

Sql 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

sql 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”
sql union example

 

Sql Union All

Sql 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”

sql union all example

Difference between Sql Union and sql Union All

Union returns the distinct rows  while Union all returns all the rows. So we have to very careful while choosing these set operators

 

Sql Intersect

Sql 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.

sql 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.

Sql 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

sql 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”

sql 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

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