SET Operation in SQL, UNION, UNION ALL, Intersect and Minus
SET Operation
SQL underpins few Set operations to be performed on tables. These are utilized to get meaningful and important results from the dataset, under various exceptional conditions.
UNION
UNION is utilized to join the outputs of two or more than two Select statement. In any case, it will wipe out copy columns from its outcome set. In the event of union, number of columns and their datatypes must be same in both the tables otherwise the union will not work.
Syntax:
SELECT col_name_1, col_name_2, col_name_3, …
FROM tbl_name_1
UNION
SELECT col_name_1, col_name_2, col_name_3, …
FROM tbl_name_2
Demo Tables
Table 1 Visitors_at_Gate_1
| ID | Name | Cell# |
| 1 | Richard Edward | +44 123 456-7890 |
| 2 | Garry Kristein | +44 123 456-7980 |
| 3 | Michael David | +44 123 456-8790 |
| 4 | Rita White | +44 123 456-6786 |
| 5 | Bob Alvin | +44 123 456-4568 |
Table 2 Visitors_at_Gate_2
| ID | Name | Cell# |
| 1 | Rachael Stock | +44 123 456-1234 |
| 2 | Garry Kirstein | +44 123 456-7980 |
| 3 | Isabella Rise | +44 123 456-3456 |
| 4 | Rita White | +44 123 456-6786 |
| 5 | Jarome Kahn | +44 123 456-6656 |
Example
SELECT * FROM Visitors_at_Gate_1
UNION
SELECT * FROM Visitors_at_Gate_2
The output of the above is as follows:
| ID | Name | Cell# |
| 1 | Rachael Stock | +44 123 456-1234 |
| 1 | Richard Edward | +44 123 456-7890 |
| 2 | Garry Kirstein | +44 123 456-7980 |
| 3 | Isabella Rise | +44 123 456-3456 |
| 3 | Michael David | +44 123 456-8790 |
| 4 | Rita White | +44 123 456-6786 |
| 5 | Bob Alvin | +44 123 456-4568 |
| 5 | Jarome Kahn | +44 123 456-6656 |
UNION ALL
Unlike UNION, UNION ALL shows all the rows regardless if they are duplicate
Syntax
SELECT col_name_1, col_name_2, col_name_3, …
FROM tbl_name_1
UNION ALL
SELECT col_name_1, col_name_2, col_name_3, …
FROM tbl_name_2
Example
SELECT * FROM Visitors_at_Gate_1
UNION ALL
SELECT * FROM Visitors_at_Gate_2
Output of the above query will be:
| ID | Name | Cell# |
| 1 | Richard Edward | +44 123 456-7890 |
| 1 | Rachael Stock | +44 123 456-1234 |
| 2 | Garry Kristein | +44 123 456-7980 |
| 2 | Garry Kirstein | +44 123 456-7980 |
| 3 | Michael David | +44 123 456-8790 |
| 3 | Isabella Rise | +44 123 456-3456 |
| 4 | Rita White | +44 123 456-6786 |
| 4 | Rita White | +44 123 456-6786 |
| 5 | Bob Alvin | +44 123 456-4568 |
| 5 | Jarome Kahn | +44 123 456-6656 |
Intersect
Intersect operation is utilized to consolidate the output of two SELECT statements, however it will just return the records that are same from both SELECT statements. In intersect, the columns and their datatypes must be same.
Syntax
The syntax for intersect is:
SELECT col_name_1, col_name_2, col_name_3, …
FROM tbl_name_1
UNION ALL
SELECT col_name_1, col_name_2, col_name_3, …
FROM tbl_name_2
Example
SELECT * FROM Visitors_at_Gate_1
INTERSECT
SELECT * FROM Visitors_at_Gate_2
The output of the above query will be:
| ID | Name | Cell# |
| 2 | Garry Kirstein | +44 123 456-7980 |
| 4 | Rita White | +44 123 456-6786 |
Minus
Minus operation merges the results of two Select statements and extracts only the results that belongs to the first set and are not matching the second set.
Syntax
The syntax for intersect is:
SELECT col_name_1, col_name_2, col_name_3, …
FROM tbl_name_1
MINUS
SELECT col_name_1, col_name_2, col_name_3, …
FROM tbl_name_2
Example
SELECT * FROM Visitors_at_Gate_1
MINUS
SELECT * FROM Visitors_at_Gate_2
The output of the above query will be:
| ID | Name | Cell# |
| 1 | Richard Edward | +44 123 456-7890 |
| 3 | Michael David | +44 123 456-8790 |
| 5 | Bob Alvin | +44 123 456-4568 |
More Related Articles For You
- Architecture of DBMS
- CODDS RULE DBMS
- Database Models in DBMS
- Relational DBMS Concepts
- Keys and Types of keys in Database
- Database Normalization
- Generalization, Specialization and Aggregation Concepts in DBMS
- ERD Diagram Tutorial with Examples in DBMS
- Introduction to SQL
- How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
- Alter Query Command in SQL, Add, Modify and Drop Column in Table
- TCL Commands in SQL
- Truncate Query, Drop and Rename Query in SQL
- All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
- Data Control Language DCL Revoke and Grant Command in SQL
- Select Statement or Select Query in SQL Explained with Examples
- Distinct Keyword Explained in SQL
- WHERE Statement or WHERE Clause in SQL Statement Explained
- AND & OR Operators in SQL
- LIKE Operator in SQL
- ORDER BY Clause Sorting Explained in SQL
- Group By Clause in SQL
- Having Clause Explained in SQL
- SQL Constraints
- SQL Aggregate Functions
- SQL Aliases Use and Purpose in SQL
- SQL Joins Types, Use and Purpose
- SQL Sequence Syntax and Use with Examples
- SQL View