SQL Set Operators

Manipulation of data often requires various operations to be done. One of the most popular operations is the Set Operation. This article will give you a detailed walkthrough of all SQL Set Operators that you can use to perform data manipulation.

Table of contents

• What are Set Operators
• UNION Operator
• UNION ALL Operator
• INTERSECT Operator
• MINUS Operator
• Conclusion

In this article, we will consider the following tables to explain to you the set operators in SQL.

Patients Table

Patient ID	Patient Name	Sex	Age	Address	Postal Code	State
01	Sheela	F	23	Flat no 201, Vasavi Heights, Yakutapura	500023	Telangana
02	Rehan	M	21	Building no 2, Yelahanka	560063	Karnataka
03	Anay	M	56	H No 1, HSR Layout	560064	Karnataka
04	Mahira	F	42	House no 12, Gandhinagar	500023	Telangana
05	Nishant	M	12	Sunflower Heights, Thane	400080	Maharashtra

Doctors Table

Doctor ID	DoctorName	Sex	Age	Address	Postal Code	State
01	Rohan	F	23	Flat no 203, Vasavi Heights, Yakutapura	500023	Telangana
02	Amayra	F	21	Building no 4, Majestic Colony	560091	Karnataka
03	Sohan	M	56	HNo 3, SCB	560090	Karnataka
04	Pihu	F	42	House no 12, Gandhinagar	500028	Telangana
05	Karan	M	12	Gulmohar Heights, Navi Mumbai	400082	Maharashtra

What are Set Operators

Set Operators are used to perform set operations on two or more tables to manipulate data. These are primarily used in complex queries to retrieve data from related tables. There are various set operations that you can perform. Refer to the image below.

Next, in this article, let us understand the UNION Operator.

Also explore:100+ SQL Interview Questions and Answers for 2022

Also explore: Database and SQL courses certification

Also explore: Top 30 SQL Query Interview Questions

UNION Operator

The SQL UNION Operator combines two or more SQL SELECT statements and generates unique values as output.  Here, you must remember that each SELECT statement used in the UNION clause must have the same number of columns with the same data type.

Basic Example:

Syntax:

SELECT Column1, Column2, … ColumnN FROM Table 1 UNION SELECT Column1, Column2, …, ColumnN FROM Table2; 

Example:

SELECT Age FROM Patients UNION Age FROM Doctors;

Retrieve DISTINCT Postal Codes:

Example:

SELECT PostalCode FROM Patients
UNION
SELECT PostalCode FROM Doctors
ORDER BY PostalCode;

Retrieve data and give a pseudo name to a column:

Example:

SELECT ‘PostalCode’ AS ‘PNO’, State FROM Patients
UNION
SELECT ‘PostalCode’, State FROM Doctors;

Retrieve data with conditions:

Example:

SELECT ‘PostalCode’ AS ‘PNO’, State FROM Patients
UNION
SELECT ‘PostalCode’, State FROM Doctors;

Retrieve data using SQL JOINS:

For this example, I will consider the following table apart from the above two tables:

Payment Details

PaymentID	PatientID	Fees
1123	2	900
3211	1	800
6543	2	800
6789	3	700
9876	4	600

Example:

SELECT  PatientID, PatientName, Fees
   FROM Patients
   LEFT JOIN PaymentDetails
   ON Patients.PatientID = PaymentDetails.PatientID
UNION
   SELECT  PatientID, PatientName, Fees
   FROM Patients
   RIGHT JOIN PaymentDetails
   ON Patients.PatientID = PaymentDetails.PatientID;   

Next, let us understand the UNION ALL operator.

UNION ALL Operator

The SQL UNION ALL operator combines two or more SQL SELECT statements and generates output. Here, you must remember that each SELECT statement used in the UNION ALL clause must have the same number of columns with the same data type. The output generated will also include duplicate values.

Basic Example:

Syntax:

SELECT Column1, Column2, …, ColumnN FROM Table 1 UNION ALL SELECT Column1, Column2, …, ColumnN FROM Table2;      

Example:

SELECT Age FROM Patients UNION ALL Age FROM Doctors;

Retrieve all Postal Codes from both the tables:

Example:

SELECT PostalCode FROM Patients
UNION ALL
SELECT PostalCode FROM Doctors
ORDER BY PostalCode;       

Retrieve data and give a pseudo name to a column:

Example:

SELECT ‘PostalCode’ AS ‘PNO’, State FROM Patients
UNION ALL
SELECT ‘PostalCode’, State FROM Doctors;
              

Retrieve data with conditions:

Example:

SELECT Address, PostalCode, State FROM Patients
WHERE State='Telangana'
UNION ALL
SELECT Address, PostalCode, State FROM Doctors
WHERE Country='Telangana'
ORDER BY State;
       

Retrieve data using SQL JOINS:

Example:

SELECT  PatientID, PatientName, Fees
   FROM Patients
   LEFT JOIN PaymentDetails
   ON Patients.PatientID = PaymentDetails.PatientID
UNION ALL
   SELECT  PatientID, PatientName, Fees
   FROM Patients
   RIGHT JOIN PaymentDetails
   ON Patients.PatientID = PaymentDetails.PatientID;
          
Moving ahead in this article, let us understand the INTERSECT operator.

INTERSECT Operator

The SQL INTERSECT operator combines two or more SQL SELECT statements and generates an output, i.e., the intersection of them. So, it retrieves the common values from both tables. 

Basic Example:

Syntax:

SELECT Column1, Column2, …, ColumnN FROM Table 1 INTERSECT SELECT Column1, Column2, …, ColumnN FROM Table2; 

Example:

SELECT Age FROM Patients INTERSECT Age FROM Doctors;           

Finally, let us understand the MINUS operator.

MINUS Operator

The SQL MINUS Operator returns only the records present in one of the tables and not the other table. 

Basic Example:

Syntax:

SELECT Column1, Column2, …, ColumnN FROM Table 1 MINUS SELECT Column1, Column2, …, ColumnN FROM Table2;            

Example:

SELECT Age FROM Patients MINUS Age FROM Doctors;              

Conclusion

With this, we end this article on SQLSet Operators. We hope you found it informative. You can refer to the article on MySQL Commands for a detailed walkthrough of all commands.

Top Trending Articles:
Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst