Union and Union All Command in SQL Server

In this article, we will learn the following:

1. When to use Union and Union All.
2. Syntax rules.
3. Difference between Union and Union All.
4. Difference between Join and Union.

For this demo, I will be using these two tables:



The following the query for creating the preceding two tables.

CREATE DATABASE db_Union;
USE db_Union;

CREATE TABLE tblDiplomaStudent(
   Id INT IDENTITY PRIMARY KEY,
   Name NVARCHAR(50),
   Gender NVARCHAR(10),
);
INSERT INTO tblDiplomaStudent VALUES ('Sara','Female'),('Aiden','Male'),('Michael','Male');

CREATE TABLE tblBachelorStudent(
   Id INT IDENTITY PRIMARY KEY,
   Name NVARCHAR(50),
   Gender NVARCHAR(10),
);
INSERT INTO tblBachelorStudent VALUES ('Lara','Female'),('Aiden','Male'),('Trevor','Male');

Purpose or use of union and union all

If there is a need to combine the result-set of two or more SELECT queries, then we use Union and Union All.

Union Example

SELECT Id,Name,Gender FROM tblDiplomaStudent
UNION
SELECT Id,Name,Gender FROM tblBachelorStudent;

In the preceding queries we are joining the two result-sets of SELECT queries.

Execute it.



We got five results from both of the tables.

Let's see how to do the same thing using UNION ALL.

Union All Example

SELECT Id,Name,Gender FROM tblDiplomaStudent
UNION ALL
SELECT Id,Name,Gender FROM tblBachelorStudent;

Execute it.



This time we got six records.

Difference between Union and Union All

"Union" removes duplicate rows whereas "Union All" retrieves and displays all the rows.

If you look at the output we got using Union All, the Aiden records are displayed twice, meaning Aiden is a bachelor student as well as the diploma student whereas if you look at the output we got using Union, Aiden the record is displayed only once and with that you will see that the records we got using Union is sorted by default which makes it slower than Union All.

Syntax Rules

If you are retrieving result-sets from two or more tables using Union or Union All, make sure the number, data types and order of the columns in the select statements should be the same or you will get an error.

You can specify the line terminator symbol (;) only after the last SELECT query.

If you want any of the columns to be displayed in descending order, then specify the ORDER BY clause after the last SELECT statement.

Demo

SELECT Id,Name,Gender FROM tblDiplomaStudent
UNION ALL
SELECT Id,Name FROM tblBachelorStudent;

In the second Select statement, I haven't specified the third column which is “gender”. So, when I execute the preceding query, I will get an error.



Difference between Union and Join

Union combines rows from 2 or more tables whereas Joins combines columns from 2 or more tables. Union combines the result-set of two or more SELECT queries into a single result-set whereas joins retrieve data from two or more tables based on logical relationship between the tables.

I hope you like it. Thank you.