In This Article, You Will Know About SQL UNION Operator.
SQL UNION Operator – Before moving ahead, let’s know a bit about SQL FULL JOIN.
Table of Contents
SQL UNION Operator
The SQL UNION operator is used to combine a set of results from two or more SELECT statements.
Note:
- Every SELECT statement must have an equal number of columns.
- Every SELECT statement column must have contained the same data type.
- Every SELECT statement column must have the same order.
Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION ALL
By default, the UNION operator selects only unique values, but to allow duplicated values, use UNION ALL.
Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Example Database
Table name: Data_1
S.No | Name | Class | Section |
1 | Alex | 5 | A |
2 | Rolex | 6 | B |
3 | Salex | 7 | B |
4 | Jalex | 8 | D |
5 | Kalex | 9 | E |
Table name: Data_2
S.No | Name | Class | Section |
6 | Zoe | 10 | A |
7 | John | 11 | B |
8 | Smith | 12 | B |
9 | Salex | 7 | B |
10 | Jalex | 8 | E |
SQL UNION Example
Example: Use the UNION operator to return the set of results from two tables.
SELECT Name FROM Data_1
UNION
SELECT Name FROM Data_2
ORDER BY Name;
As a result, it returns unique queries from both table’s Name columns.
SQL UNION ALL Example
Example: Use the UNION operator to return the set of results from two tables.
SELECT Name FROM Data_1
UNION ALL
SELECT Name FROM Data_2
ORDER BY Name;
As a result, it returns all queries (including duplicates) from both tables’ Name columns.
SQL UNION With WHERE
SELECT Name, Section FROM Data_1
WHERE Section = 'B'
UNION
SELECT Name, Section FROM Data_2
WHERE Section = 'B'
ORDER BY Name;
As a result, it returns unique queries from both table’s Name & Section columns.
SQL UNION ALL With WHERE
SELECT Name, Section FROM Data_1
WHERE Section = 'B'
UNION ALL
SELECT Name, Section FROM Data_2
WHERE Section = 'B'
ORDER BY Name;
As a result, it returns unique queries from both table’s Name & Section columns.
Another UNION Example
SELECT 'Student-A' AS Type, Name, Class, Section
FROM Data_1
UNION
SELECT 'Student-B' AS Type, Name, Class, Section
FROM Data_2;
As a result, it returns all queries from both tables’ Name, Class & Section columns.
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
