Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • SQL - Getting Started
  • What is SQL
  • Create Table
  • ALTER TABLE Statements
  • Rename Columns
  • Modify Column Type
  • Drop Columns
  • Rename Tables
  • Drop Tables
  • Insert Statement
  • Update Statement
  • Delete Statement
  • Truncate Statement
  • Merge Statement
  • Null Value
  • Select Query
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • SQL - Inner Join
  • SQL - Left Join
  • SQL - Right Join
  • SQL - Full Join
  • SQL - BETWEEN
  • SQL - IN
  • SQL - LIKE
  • SQL - INTERSECT
  • SQL - MINUS
  • SQL - UNION
  • SQL - UNION ALL
  • SQL - DISTINCT
  • SQL - ANY, SOME
  • SQL - ALL
  • SQL - AVG()
  • SQL - COUNT()
  • SQL - MAX()
  • SQL - MIN()
  • SQL - SUM()
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

SQL - Union All Operator

UNION ALL Operator is used to combine result set of two or more SELECT queries. The UNION ALL operator does not remove duplicate rows from SELECT statement result set.

UNION and UNION ALL operators works same. Only difference is UNION operator exclude duplicate rows from result set. UNION ALL does not remove duplicate rows from query result set.

Syntax:

SELECT column_name1, column_name2,... FROM tables [WHERE Condition] UNION ALL SELECT column_name1, column_name2, ... FROM tables [WHERE Condition];

For the demo purpose, we will use the following tables in all examples.

Employee
EmpIdFirstNameLastNameEmailSalaryHireDate
1'John''King''[email protected]'330002018-07-25
2'James''Bond'2018-07-29
3'Neena''Kochhar''[email protected]'170002018-08-22
4'Lex''De Haan''[email protected]'150002018-09-8
5'Amit''Patel'180002019-01-25
6'Abdul''Kalam''[email protected]'250002020-07-14
Employee_backup
EmpIdFirstNameLastNameEmailSalaryHireDate
1'John''King''[email protected]'330002018-07-25
2'James''Bond'2018-07-29
3'Neena''Kochhar''[email protected]'170002018-08-22
6'Abdul''K''[email protected]'250002020-07-14
7'Swati''Karia''[email protected]'220002020-09-18

Consider the following query with the UNION ALL operator.

SQL Script: UNION ALL Operator
SELECT * FROM Employee
UNION ALL
    SELECT * from Employee_backup

Above query returns all the records in both the tables even if they are duplicate records, as shown below.

EmpIdFirstNameLastNameEmailSalaryHireDate
1'John''King''[email protected]'330002018-07-25
2'James''Bond'2018-07-29
3'Neena''Kochhar''[email protected]'170002018-08-22
4'Lex''De Haan''[email protected]'150002018-09-8
5'Amit''Patel'180002019-01-25
6'Abdul''Kalam''[email protected]'250002020-07-14
1'John''King''[email protected]'330002018-07-25
2'James''Bond'2018-07-29
3'Neena''Kochhar''[email protected]'170002018-08-22
6'Abdul''K''[email protected]'250002020-07-14
7'Swati''Karia''[email protected]'220002020-09-18

Note that both the queries must have equal number of expressions in their SELECT clause. The following query will raise an error.

SQL Script: Error with UNION ALL
SELECT * FROM Employee
UNION ALL
    SELECT EmpId, FirstName from Employee_backup

You can use the WHERE clause with any or all queries, as shown below.

SQL Script: UNION ALL Query
SELECT EmpId, FirstName, LastName, Salary FROM Employee
WHERE Salary > 18000
UNION ALL
    SELECT EmpId, FirstName, LastName, Salary FROM Employee_backup
EmpIdFirstNameLastNameSalary
1'John''King'33000
6'Abdul''Kalam'25000
1'John''King'33000
6'Abdul''K'25000
7'Swati''Karia'22000
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.