EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials SQL Tutorial SQL UNION ALL

SQL UNION ALL

Priya Pedamkar
Article byPriya Pedamkar

Updated March 13, 2023

SQL UNION ALL

Introduction to SQL UNION ALL

UNION ALL operator in standard query language (SQL) is used to combine results from two or more SELECT queries into a single result set. When combining results, UNION ALL does not remove duplicate records from the final result set. A very popular comrade of this operator is UNION which is used to combine the results obtained from two or more SELECT statements but while combining the results it removes duplicate records from the final result set.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

While working with UNION or UNION ALL operators we should keep the following points in mind:

  • The number of columns in the SELECT statement on which we want to use the UNION ALL operator must be the same.
  • The selected columns should have the same data type. If not, they should at least have data types which are convertible to the same data type.
  • The order of the columns must be in the same order as mentioned in the SELECT statement.

For the uninitiated, a pictorial depiction of UNION ALL command.

pictorial depiction of UNION ALL

Syntax and Parameters

The basic syntax for writing SELECT queries with UNION ALL operators is as follows:

SELECT column_name
FROM table_name_1
UNION ALL
SELECT column_name
FROM table_name_2
UNION ALL
SELECT column_name
FROM table_name_3
.
.
.

The parameters used in the above mentioned syntax are as follows:

  • column_name: Specify the column name on which you want to perform UNION ALL operation and want it to feature in the result set.
  • FROM table_name_1: Specify the first table name from which the column has to be fetched.
  • FROM table_name_2: Specify the second table name from which the column has to be fetched.

Of the above mentioned parameters, all the parameters are mandatory. You may feel free to use WHERE, GROUP BY and HAVING clauses based on your requirement.

Examples of SQL UNION ALL

Given below are the examples mentioned:

In order to illustrate usage and functionality of UNION ALL operator in SQL, let us create two dummy tables “sales_april” and “sales_may”. These tables have similar structure and they contain details pertaining to salesperson, sales made, sales target, store location etc.

Code:

CREATE TABLE public.sales_april
(
salesperson_id integer NOT NULL,
salesperson character varying(255) NOT NULL,
store_state character varying(255) NOT NULL,
sales_target numeric NOT NULL,
sales_current numeric NOT NULL
);

Output:

SQL UNION ALL 1

Code:

CREATE TABLE public.sales_may
(
salesperson_id integer NOT NULL,
salesperson character varying(255) NOT NULL,
store_state character varying(255) NOT NULL,
sales_target numeric NOT NULL,
sales_current numeric NOT NULL
);

Output:

SQL UNION ALL 2

Let us insert some data records in sales_april and sales_may tables.

Code:

INSERT INTO sales_april
(salesperson_id
,salesperson
,store_state
,sales_target
,sales_current)
VALUES
(101,'Danish K','KA',10000,10000),
(102,'Rashmi Sharma','DL',23000,18000),
(103,'Mohak Patel','MH',21000,21000),
(104,'Devika Ramaswamy','TN',10000,8000),
(105,'Reema Ray','WB',0,10000);
INSERT INTO sales_may
(salesperson_id
,salesperson
,store_state
,sales_target
,sales_current)
VALUES
(106,'Rohit Khanna','PB',10000,10000),
(102,'Rashmi Sharma','DL',13000,12000),
(107,'Hardik Mahajan','RJ',20000,19000),
(104,'Devika Ramaswamy','TN',10000,18000),
(105,'Reema Ray','WB',10000,10000);

Example #1

Find all the salespersons who have worked for the departmental store during the month of April and May.

Code:

SELECT salesperson
FROM sales_april
UNION ALL
SELECT salesperson
FROM sales_may;

Output:

salespersons who have worked for the departmental store

In this example, we have used the UNION ALL operator to combine results from sales_april and sales_may tables. We observe that we have received duplicate values such as Reema Ray, Rashmi Sharma etc.

Consider this query now.

Code:

SELECT salesperson
FROM sales_april
UNION
SELECT salesperson
FROM sales_may;

Output:

SQL UNION ALL 4

Here, we have used the UNION operator to combine results from sales_april and sales_may tables similar to previous query. But unlike UNION ALL operator, we did not get duplicate values in case of UNION operator.

Example #2

Find all the salespersons and the store locations, who worked for the departmental store during April and May and have made sales for more than $15000.

Code:

SELECT salesperson, store_state, sales_current
FROM sales_april
WHERE sales_current > 15000
UNION ALL
SELECT salesperson, store_state, sales_current
FROM sales_may
WHERE sales_current < 15000;

Output:

salespersons and the store locations

Here, we have used the UNION ALL function to combine results from sales_april and sales_may tables.

Example #3

Find all the store locations along with the total sales target and total current sales.

Code:

WITH CTE AS (SELECT *
FROM sales_april
UNION ALL
SELECT *
FROM sales_may)
SELECT store_state, sum(sales_target) as sales_target, sum(sales_current) as sales_current
FROM CTE
GROUP BY store_state
ORDER BY sales_current DESC;

Output:

Find all the store locations

In this example, we have first combined the results from sales_april and sales_may tables and then found aggregate sales_target and sales_current for each store by grouping the records by store_state field.

Conclusion

UNION ALL operator is used as a combinator to combine results from two or more SELECT statements. However, it returns duplicate records in the final result set.

Recommended Articles

We hope that this EDUCBA information on “SQL UNION ALL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Array in SQL
  2. SQL While Loop
  3. Metadata in SQL
  4. SQL REGEXP
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW