Get Started: SQL UNION Operator

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.

Connect on:

Leave a Comment

Stay in the loop

codingstreets