Generally when we work on any application that application stored data into multiple SQL tables. Sometimes we use multiple Select statements to retrieve data from multiple SQL tables and combine the result set of all Select statements. We use the SQL UNION or UNION ALL operator to combine two or more Select statement result sets.

The syntax for the SQL UNION operator 
SELECT ColumnName1, ColumnName2 ...ColumnName (N) FROM table1
UNION
SELECT ColumnName1, ColumnName2 ...ColumnName (N) FROM table2;
The syntax for the SQL UNION ALL operator 
SELECT ColumnName1, ColumnName2 ...ColumnName (N) FROM table1
UNION ALL
SELECT ColumnName1, ColumnName2 ...ColumnName (N) FROM table2;
We should keep in mind the following points to write a query with the SQL UNION or UNIION ALL Operator.

  • All the Select statements must have the same number of columns
  • Columns data types and order must also match in both the Select statement
  • We can not define GROUP BY and Having Clause with the result set generated from the UNION/UNION ALL of both Select statements rather we need to define it with each Select statement.

Let's prepare some test data to understand it with an example 
CREATE TABLE TblIndiaStudents
(
SRNo NVARCHAR(20),
Name NVARCHAR(40),
Gender NVARCHAR(10)
)

GO

INSERT INTO TblIndiaStudents(SRNo,Name,Gender) VALUES
('BBA001','Kamal','Male'),
('BBA002','Raj','Male'),
('BBA003','Maria','Female')

GO

CREATE TABLE TblUKStudents
(
SRNo NVARCHAR(20),
Name NVARCHAR(40),
Gender NVARCHAR(10)
)

GO

INSERT INTO TblUKStudents(SRNo,Name,Gender) VALUES
('BBA003','Maria','Female'),
('BBA004','Sam','Male'),
('BBA005','Marian','Male')
Result
1. Use of the UNION operator to combine the rows of TblIndiaStudents and TblUKStudents.
SELECT SRNo,Name,Gender FROM TblIndiaStudents
UNION 
SELECT SRNo,Name,Gender FROM TblUKStudents
Result
2. Use of the UNION ALL operator to combine the rows of TblIndiaStudents and TblUKStudents.
SELECT SRNo,Name,Gender FROM TblIndiaStudents
UNION ALL
SELECT SRNo,Name,Gender FROM TblUKStudents
Result
From the output, it is clear that UNION operator removes duplicate rows, whereas UNION ALL does not.

Question: When would you use UNION in SQL?
We can use UNION when we want to remove duplicate records from the result set.

Question: When would you use UNION ALL in SQL?
It's just the opposite of UNIION so we can use UNION ALL when we don't want to remove duplicate records from the result set.

Note: There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows.

We cannot use Order By clause with each Select statement rather we need to use it with the result set generated from the UNION/UNION ALL of both Select statements as given below
SELECT SRNo,Name,Gender FROM TblIndiaStudents
UNION ALL
SELECT SRNo,Name,Gender FROM TblUKStudents
ORDER BY SRNo