Mastering the UNION Operator in SQL: A Comprehensive Guide

The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order

Syntax: UNION

SELECT column_name(s) FROM tableA
UNION
SELECT column_name(s) FROM tableB;

Syntax: UNION ALL

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL

SELECT column_name(s) FROM tableA
UNION ALL
SELECT column_name(s) FROM tableA;

Code:

 -- USE Database
USE SQLExamples

DROP TABLE Subjects
DROP TABLE Chapters

CREATE TABLE Subjects(
	SubjectId INT,	
	SubjectName VARCHAR(30)
)


INSERT INTO Subjects VALUES 
(1, 'C'),
(2, 'Java'),
(3, 'Python'),
(4, 'PHP'),
(5, 'PHP')

SELECT * FROM Subjects

CREATE TABLE Chapters(
	ChapterId INT,	
	ChapterName VARCHAR(30),
	SubjectId int,
)

INSERT INTO Chapters VALUES
(1, 'Introduction C', 1),
(2, 'Datatypes C', 1), 
(3, 'Introduction Java', 2), 
(4, 'Introduction Java', 2) 

SELECT * FROM Chapters

Code:

SELECT SubjectName FROM Subjects
UNION
SELECT ChapterName FROM Chapters

Output:

The above code will produce the following result-

SubjectName

C

Datatypes C

Introduction C

Introduction Java

Java

PHP

Python

Example: UNION ALL

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL: <.p>

Code:

SELECT SubjectName FROM Subjects
UNION ALL
SELECT ChapterName FROM Chapters

Output:

The above code will produce the following result-

SubjectName

C

Java

Python

PHP

PHP

Introduction C

Datatypes C

Introduction Java

Introduction Java

Differences between UNION and UNION ALL (Common Interview Question)

From the output, it is very clear that, UNION removes duplicate rows, where as UNION ALL does not. When use UNION, to remove the duplicate rows, sql server has to to do a distinct sort, which is time consuming. For this reason, UNION ALL is much faster than UNION. 

Note: If you want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L.

Note: For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be same.

Difference between JOIN and UNION

JOINS and UNIONS are different things. However, this question is being asked very frequently now. UNION combines the result-set of two or more select queries into a single result-set which includes all the rows from all the queries in the union, where as JOINS, retrieve data from two or more tables based on logical relationships between the tables. In short, UNION combines rows from 2 or more tables, where JOINS combine columns from 2 or more table.