SQL UNION ALL Operator

SQL UNION ALL operator is used to combine the results of two or more SELECT statements. Each SELECT statement within the UNION ALL must have the same number of columns and all columns should have same order with similar data types. To allow all duplicate values, you can use UNION ALL operator as SQL UNION operator returns only distinct values by default.

Syntax

SELECT column1, column2, column3,…columnN FROM tableName1
UNION ALL
SELECT column1, column2, column3,…columnN FROM tableName2

Here is our first table Student.

We have second table as NewStudents.

Now, we will use UNION ALL operator to combine records of both the tables Student & NewStudents.

So, here is the output.

SELECT *FROM Student
UNION ALL
SELECT *FROM NewStudents

Similarly, you can combine specific columns columns from two tables as given below.

SELECT RollNumber, FirstName, Country, Fees FROM Student
UNION ALL
SELECT RollNumber, FirstName, Country, Fees FROM NewStudents

Note: SQL UNION ALL operator returns all records including duplicates by default. To allow only distinct values use UNION operator.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like

• 

  Activity Monitor in SQL Server 2012
• 

  SQL AND & OR Operators
• 

  Create Automated Maintenance Jobs with SQL Server Maintenance Plan
• 

  SQL Server Data Components – How Data is Stored
• 

  SQL SELECT INTO Statement
• 

  Identify when Statistics were last Updated in SQL Server Database