Compare the Difference Between Similar Terms

Difference Between

Home / Technology / IT / Database / Difference Between Union and Union All in SQL Server

Difference Between Union and Union All in SQL Server

June 7, 2018 Posted by Lithmee

The key difference between union and union all in SQL server is that union gives the resulting dataset without duplicate rows while union all gives the resulting dataset with the duplicate rows.

DBMS is a software to create and manage databases. A database consists of many tables, and the tables are related to each other. DBMS helps to perform operations such as creating databases, creating tables, inserting and updating data and many more. Furthermore, it secures data and reduces data redundancy for data consistency. SQL server is one such DBMS. Structured Query Language (SQL) is the language to manage data in the DBMS. Union and union all are two commands in SQL that help to perform set operations in the table data.

CONTENTS

1. Overview and Key Difference
2. What is Union in SQL Server
3. What is Union All in SQL Server
4. Side by Side Comparison – Union vs Union All in SQL Server in Tabular Form
5. Summary

What is Union in SQL Server?

Sometimes it is necessary to perform set operations in SQL. Union is one of them.

Difference Between Union and Union All in SQL Server

Union combines results of two or more select statements. Thereafter, it will return the result without any duplicate rows. To perform this operation, the tables should have the same number of columns and same data types. Refer the below two tables.

Difference Between Union and Union All in SQL Server_Fig 2

Difference Between Union and Union All in SQL Server Fig 3

The first table is s1 and the second table is s2. The SQL statement to perform union is as follows.

select * from s1

union

select * from s2;

It will provide the result set as follows.

Difference Between Union and Union All in SQL Server Fig 4

It gives a resulting table without the duplicate rows.

What is Union All in SQL Server?

The union all is another SQL command to perform set operations. Similar to Union, this will also combine results of two or more select statements. It is also necessary to have the same number of columns and same data types to the tables which the union all operation applies to.  Refer the below two tables.

Difference Between Union and Union All in SQL Server Fig 5

Difference Between Union and Union All in SQL Server Fig 6

Similar to before, the first table is s1 and the second table is s2. The statement to perform union all is as follows.

select * from s1

union all

select * from s2;

It will provide the result set as follows.

Difference Between Union and Union All in SQL Server Fig 7

It gives the resulting table with duplicate rows.

What is the Difference Between Union and Union All in SQL Server?

Union is an SQL command that combines the results of two or more select statements without returning any duplicate rows. Union All is an SQL command that combines the result of two or more select statements including duplicate rows. This is the key difference between union and union all in SQL server. In other words, union gives the resulting dataset without duplicate rows. On the other hand, union all gives the resulting dataset with duplicate rows.

Difference Between Union and Union All in SQL Server in Tabular Form

Summary – Union vs Union All in SQL Server

This article discussed two SQL command related to set operations, which are union and union all. The difference between union and union all SQL server is that union gives the resulting dataset without duplicate rows while union all gives the resulting dataset with the duplicate rows. SQL server executes the statements with these SQL commands.

Reference:

1.“SQL UNION.” First Normal Form (1NF) – Database Normalization. Available here
2.“SQL UNION ALL.” First Normal Form (1NF) – Database Normalization. Available here

Image Courtesy:

1.’Sql-server-ce-4-logo’By Microsoft – Microsoft Website, (Public Domain) via Commons Wikimedia

Related posts:

Difference Between Singly Linked List and Doubly Linked List Difference Between Entity and Attribute Difference Between SAP and ORACLE Difference Between Update and Alter Difference Between DBMS and File Management System

Filed Under: Database

About the Author: Lithmee

Lithmee Mandula is a BEng (Hons) graduate in Computer Systems Engineering. She is currently pursuing a Master’s Degree in Computer Science. Her areas of interests in writing and research include programming, data science, and computer systems.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Request Article

Featured Posts

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and SARS

Difference Between Coronavirus and SARS

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Covid 19

Difference Between Coronavirus and Covid 19

You May Like

Difference Between Hoodie and Sweatshirt

Difference Between Hoodie and Sweatshirt

Difference Between Hostile and Instrumental Aggression

Difference Between Hostile and Instrumental Aggression

Difference Between Regiment and Brigade

Difference Between Regiment and Brigade

Difference Between Cyst and Polyp

Difference Between Cyst and Polyp

What is the Difference Between DNA and RNA Vaccines

What is the Difference Between DNA and RNA Vaccines

Latest Posts

  • What is the Difference Between Flakiness Index and Elongation Index
  • What is the Difference Between Salmonella and Shigella
  • What is the Difference Between NTU and FTU
  • What is the Difference Between Escherichia coli and Entamoeba coli
  • What is the Difference Between Education and Socialization
  • What is the Difference Between Nurse Crop and Cover Crop
  • Home
  • Vacancies
  • About
  • Request Article
  • Contact Us

Copyright © 2010-2018 Difference Between. All rights reserved. Terms of Use and Privacy Policy: Legal.