Set Operations in DBMS

Set Operations in DBMS

 

In this article, we will learn about Set Operations in DBMS.

SQL set operations are used for combining data from one or more tables

There are 3 set operations in SQL. They are

  • UNION/UNION ALL
  • INTERSECT
  • MINUS

General Syntax for all SET operators

SELECT column name(s) FROM table1 
UNION/UNION ALL/INTERSECT/MINUS 
SELECT column name(s) FROM table2

 

Set Operations in DBMS

Each SELECT statement that is used this set operators must follow these conditions

  • The same number of columns
  • The columns must also have similar data types
  • The columns in each SELECT statement must also be in the same order

 

UNION/UNION ALL

 UNION clause used to combine the result-set of two or more select queries

Customers table:

CityCountry
ThimpuGermany
HyderabadIndia
HyderabadIndia
union

Suppliers table:

CityCountry
LondonUK
CaliforniaUSA
TexasUSA

 

Example for UNION

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Output:

City
Texas
Thimpu
London
Hyderabad
California

 

UNION all in SQL

The only difference between UNION and UNION all Clause is that UNION removes duplicate values while comparing where UNION ALL allows duplicate values while combining

Example for UNION ALL

SELECT City, Country FROM Customers;
UNION ALL
SELECT City, Country FROM Suppliers

Output:

CityCountry
ThimpuGermany
HyderabadIndia
HyderabadIndia
LondonUK
CaliforniaUSA
TexasUSA

INTERSECT

  • INTERSECT statement combines result-set of two or more select queries and returns only those values that are common in both the result set
  • Simply INTERSECT returns the common rows are values common in both the data sets and will not consider duplicate values

Example of INTERSECT

The First table,

ID

NAME

1

rishi
2

trish

3

mahi

The Second table,

ID

NAME

2

trish

3

Chester

4

mahi

INTERSECT query will be,

SELECT name FROM First
INTERSECT
SELECT name FROM Second;

The result set table will look like

NAME

trish

mahi

MINUS

  • MINUS operator is used to subtract the result set obtained by the first SELECT query from the result set obtained by the second SELECT query.
  • Simply, we can say that MINUS operator will return only those rows which are unique in only first SELECT query and not those rows which are common to both first and second SELECT queries.

Consider the two tables

Table1

NameAddressAgeGrade
PriyaHyderabad19A
RahulChennai20B
KarthikMumbai21A
PayalDelhi20B

Table2

NameCourseAgeGrade
DivyaJava22B
AmithaC++19A
HarshithaPython21A
PayalJava20B

Example

SELECT Name FROM Table1
MINUS
SELECT Name FROM Table2;

Output:

Name
Priya
Rahul
Karthik
MINUS