Difference Between UNION and UNION ALL
UNION and UNION ALL are set operators in SQL used to concatenate the results of two or more SELECT statements. Still, they differ from each other in how they handle duplicate records. The UNION command combines two or more SELECT statements but removes duplicates from the final result set, whereas the UNION ALL statement does not remove the duplicate from the result. UNION ALL simply concatenates all records, including duplicates from the SELECT statement. This article will explore the difference between UNION and UNION ALL.
UNION and UNION ALL in SQL are set operators that combine the results of two SELECT queries. Both share standard features, with one significant difference: UNION only returns a unique record, while UNION ALL returns all the records (including duplicates). This article will explore the difference between UNION and UNION ALL.
Must Check: Free Database and SQL Online Course and Certificates
Must Check: SQL tutorial
Table of Content
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
UNION vs. UNION ALL
| Parameter | UNION | UNION ALL |
| Definition | Combines the result from the multiple tables and returns the distinct records into a single result set. | Combines the result from the multiple tables and returns all the records into a single result set. |
| Removal of duplicates | It has a default feature to eliminate duplicate rows from the table. | It can’t eliminate the duplicate rows from the table. |
| Performance | Slow performance as it removes duplicate rows from the output. | Fast performance as compared to UNION. |
| Syntax | SELECT column_names FROM table_1 WHERE conditions UNION SELECT column_names FROM table_2 WHERE conditions |
SELECT column_names FROM table_1 WHERE conditions UNION ALL SELECT column_names FROM table_2 WHERE conditions |
Read Also: Update Query in SQL
What is a UNION?
UNION in SQL combines data from two SELECT queries' results into a single distinct result set. The result set to produce from UNION doesn’t contain any duplicate value.
Syntax
SELECT column_namesFROM table_1WHERE conditions
UNION
SELECT column_namesFROM table_2WHERE conditions;
Must Read: SQL Logical Operator
What is UNION ALL?
UNION ALL is an extension of UNION in SQL. As the result set of UNION of two tables doesn’t contain all the records from both tables, UNION ALL comes into the picture and returns all the records from both tables (including duplicate rows also).
Syntax
SELECT column_namesFROM table_1WHERE conditions
UNION ALL
SELECT column_namesFROM table_2WHERE conditions;
Also Read: How to use UNION in SQL
Enrol now in leading online courses from top colleges to explore the world of SQL and databases. Advance your career in database management.
Rules
- The SELECT statements must have the same number of columns and data types.
- Columns in both the SELECT statements must be in the same order.
- The names of the columns can be different, but the final result set will show the column name of the first selected query.
Let’s understand the difference between UNION and UNION ALL by an example.
Must Read: SQL LIMITS
Discover the pinnacle of job-centric courses after 12th. Embark on a journey to a rewarding career with specialized online degree programs.
Example
Let us have two tables of employee names and manager names. Combine both the tables using UNION and UNION ALL.
Employee
| Employee_id | Name |
| 1 | Ram |
| 2 | Shyam |
| 3 | Joya |
| 4 | Vidit |
Manager
| Employee_id | Name |
| 1 | Ram |
| 4 | Vidit |
UNION
SELECT Employee_id, nameFROM EmployeeUNIONSELECT *FROM Manager;
output
| Employee_id | Name |
| 1 | Ram |
| 2 | Shyam |
| 3 | Joya |
| 4 | Vidit |
UNION ALL
SELECT Employee_id, nameFROM EmployeeUNIONSELECT *FROM Manager;
output
| Employee_id | Name |
| 1 | Ram |
| 2 | Shyam |
| 3 | Joya |
| 4 | Vidit |
| 1 | Ram |
| 4 | Vidit |
Must Read: All about DML Command in SQL
Must Read: SQL ACID Properties
Key differences
- Both UNION and UNION ALL combine the result of two or more tables.
- The result set of UNION does not contain duplicate rows, while the result set of UNION ALL returns all the rows from both tables.
- The execution time of UNION ALL is less than the execution time of UNION as it does not remove the duplicate rows.
Conclusion
UNION and UNION ALL in SQL are used to retrieve data from two or more tables. UNION returns distinct records from both tables, while UNION ALL returns all the records from both tables.
In the article, we have briefly discussed UNION and UNION ALL, the differences between them, and examples.
Hope you will like the article.
Keep Learning!!
Keep Sharing!!
Related Reads:
Read Later
FAQs
What is UNION in SQL?
UNION in SQL combines data from the result set of two SELECT queries into a single distinct result set. The result set to produce from UNION does not contain any duplicate value.
What is UNION ALL in SQL?
UNION ALL is an extension of UNION in SQL. As the result set of UNION of two tables doesnu2019t contains all the records from both the table, so UNION ALL comes into the picture that returns all the records from both the table (including duplicate rows also).
What is the difference between UNION and UNION ALL?
The result set of UNION does not contain duplicate rows, while the result set of UNION ALL returns all the rows from both tables. The execution time of UNION ALL is less than the execution time of UNION as it does not removes the duplicate rows.
When to use: UNION vs UNION ALL?
Use UNION when you need a result set that must be free of duplicates. It's ideal when the uniqueness of rows is important for your query or analysis.
Are there any requirements for the SELECT statements used in UNION and UNION ALL?
Yes, the SELECT statements must have the same number of columns in the result set, and the columns must be of compatible types. The columns do not need to have the same names, but they must be in the same order.
How does UNION handle NULL values?
In SQL, NULL values are treated as distinct from each other. Therefore, if a column contains NULL in different rows, UNION will not consider these rows as duplicates and will include them in the result set.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
