SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

SQL SET Operator

In this tutorial, we will understand the operator who falls under the SET operator in SQL with the help of different examples.

The SQL SET Operator is used to merge the output of two or more than two SELECT queries.

The operator falls under the SET operator category is as follows:

  1. SQL UNION Operator
  2.  SQL UNION ALL Operator
  3.  SQL INTERSECT Operator
  4.  SQL MINUS Operator

There are some rules and regulations to be followed to execute queries using the SET Operator in SQL. Rules are as follows:

  1.  There should be equal numbers of columns, and the order of columns must be the same between both the tables.
  2.  Data Types must be compatible.

Let's learn the SQL SET Operator in detail with the help of an example.

Consider the already existing table with the following data

Table Number 1: Student

Student_IdStudent_NameCityAge
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
5Sakshi PatilAurangabad22
6Shruti SharmaMumbai21
7Pranoti ShendeAurangabad23
8Harshada DhanwatNashik24
9Tejas BairagiNashik21
10Nikhil PatilPune24
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
13Bhushan PachpandeBangalore25
14John ChaudharyBangalore24
15Sonakshi SenBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad23

Table Number 2: Students

Student_IdStudent_NameCityAge
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
5Swati SharmaPune22
6Nikita IngaleMumbai21
7Pranoti ShendeAurangabad23
8Harshada KakadeNashik24
9Tejas BairagiNashik21
10Naman SharmaAurangabad24
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
13Bhavesh JainBangalore25
14Jayesh NikamBangalore24
15Sonalika ShindeBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad23

1. SQL UNION Operator

  • SQL UNION Operator is used to join or combine the two or more than two SELECT queries.
  • The common records are not considered in the output result obtained after the UNION operator is executed.

The syntax for SQL UNION Operator is as follows:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_1 UNION SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_2;

Example: Write a query to execute UNION operation between student table and students table.

SELECT * FROM Student UNION SELECT * FROM Students;

In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table and executes a UNION operation with the data retrieved by the second SELECT statement from the Students table.

The output of the above query is as follows:

Student_IdStudent_NameCityAge
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
5Sakshi PatilAurangabad22
6Shruti SharmaMumbai21
7Pranoti ShendeAurangabad23
8Harshada DhanwatNashik24
9Tejas BairagiNashik21
10Nikhil PatilPune24
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
13Bhushan PachpandeBangalore25
14John ChaudharyBangalore24
15Sonakshi SenBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad23
5Swati SharmaPune22
6Nikita IngaleMumbai21
8Harshada KakadeNashik24
10Naman SharmaAurangabad24
13Bhavesh JainBangalore25
14Jayesh NikamBangalore24
15Sonalika ShindeBangalore25
SQL SET Operator

2. SQL UNION ALL Operatorss

  • SQL UNION ALL Operator merges all the records from the SELECT statement used in the query.
  • Common data are not displayed in the output in the SQL UNION operator. Still, SQL UNION ALL operator allows common data to be displayed in the output obtained after the UNION ALL operation is executed.

The syntax for SQL UNION ALL Operator is as follows:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_1 UNION ALL SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_2;

Example 1: Write a query to execute UNION ALL operation between student table and student’s table.

SELECT * FROM Student UNION ALL SELECT * FROM Students;

In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table and executes a UNION ALL operation with the data retrieved by the second SELECT statement from the Students table.

The output of the above query is as follows:

Student_IdStudent_NameCityAge
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
5Sakshi PatilAurangabad22
6Shruti SharmaMumbai21
7Pranoti ShendeAurangabad23
8Harshada DhanwatNashik24
9Tejas BairagiNashik21
10Nikhil PatilPune24
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
13Bhushan PachpandeBangalore25
14John ChaudharyBangalore24
15Sonakshi SenBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad23
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
5Swati SharmaPune22
6Nikita IngaleMumbai21
7Pranoti ShendeAurangabad23
8Harshada KakadeNashik24
9Tejas BairagiNashik21
10Naman SharmaAurangabad24
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
13Bhavesh JainBangalore25
14Jayesh NikamBangalore24
15Sonalika ShindeBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad23
SQL SET Operator

Example 2: Write a query to execute UNION ALL operation between student table and students tables. Display only those records from the Student table where the student resides in the 'Bangalore', 'Hyderabad', and 'Nashik’ cities.

SELECT * FROM Student WHERE City IN ('Nashik', 'Bangalore', 'Hyderabad') UNION ALL SELECT * FROM Students;

In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table where the student resides in the 'Nashik’, 'Bangalore', and 'Hyderabad' cities. It executes a UNION ALL operation with the data retrieved by the second SELECT statement from the Students table.

The output of the above query is as follows:

Student_IdStudent_NameCityAge
3Sourabh ChougaleNashik23
8Harshada DhanwatNashik24
9Tejas BairagiNashik21
13Bhushan PachpandeBangalore25
14John ChaudharyBangalore24
15Sonakshi  SenBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad23
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
5Swati SharmaPune22
6Nikita IngaleMumbai21
7Pranoti ShendeAurangabad23
8Harshada KakadeNashik24
9Tejas BairagiNashik21
10Naman SharmaAurangabad24
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
13Bhavesh JainBangalore25
14Jayesh NikamBangalore24
15Sonalika ShindeBangalore25
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad23
SQL SET Operator

3. SQL INTERSECTS Operators

SQL Intersect operator is used to merge two or more than two SELECT queries, but INTERSECT Operator displays only those common data between both the tables.

The syntax for SQL INTERSECT Operator is as follows:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_1 INTERSECT SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_2;

Example: Write a query to execute INTERSECTS operation between student table and students table.

SELECT * FROM Student INTERSECT SELECT * FROM Students;

In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table and executes a INTERSECTS operation with the data retrieved by the second SELECT statement from the Students table.

The output of the above query is as follows:

Student_IdStudent_NameCityAge
1Pratik SrivastavPune23
2Utkarsh RokadeMumbai22
3Sourabh ChougaleNashik23
4Prateek ZimbrePune24
7Pranoti ShendeAurangabad23
9Tejas BairagiNashik21
11Samaira SharmaMumbai22
12Anushka SenAurangabad23
16Mayuri WaghHyderabad22
17Ritika PatilHyderabad21
18Tushar MahaleHyderabad23
SQL SET Operator

4. SQL MINUS Operators:

  • SQL MINUS operator displays the rows available in the first SELECT query that are not available in the second SELECT query.
  • MINUS keyword doesn't work in SQL query. Instead of the MINUS keyword, use EXCEPT keyword to execute the MINUS operator in the SQL.

The syntax for SQL MINUS Operator is as follows:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_1 EXCEPT SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_2;

Example: Write a query to execute MINUS operation between student table and students table.

SELECT * FROM Student EXCEPT SELECT * FROM Students;

In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table and executes a MINUS operation with the data retrieved by the second SELECT statement from the Students table.

The output of the above query is given below:

Student_IdStudent_NameCityAge
5Sakshi PatilAurangabad22
6Shruti SharmaMumbai21
8Harshada DhanwatNashik24
10Nikhil PatilPune24
13Bhushan PachpandeBangalore25
14John ChaudharyBangalore24
15Sonakshi  SenBangalore25
SQL SET Operator