---
title: "Union and Union all Operator in sql"  
description: "Union and Union all both are use for combine the result-set of two of more select queries.  Let us explain both individuallyUnion-Union Operator is us"  
author: "Manish Kumar"  
published: 2017-01-18  
updated: 2018-03-17  
canonical: https://www.mindstick.com/blog/11268/union-and-union-all-operator-in-sql  
category: "database"  
tags: ["sql"]  
reading_time: 3 minutes  

---

# Union and Union all Operator in sql

[Union and Union](https://www.mindstick.com/interview/23347/when-will-union-and-union-all-behave-the-same) all both are use for combine the result-set of two of more select [queries](https://answers.mindstick.com/qa/96624/explain-queries-in-ms-access).\

Let us explain both individually

**Union-**

Union Operator is use for [merging](https://www.mindstick.com/articles/446/copying-merging-and-or-uniting-records-in-sql-server) two or more select queries. It returns [distinct](https://www.mindstick.com/forum/159558/why-c-sharp-linq-distinct-doesn-t-work) rows from the result-set.

**Let us explain by taking an example**

Make two [table in the database](https://www.mindstick.com/forum/157515/what-are-ddl-triggers-create-a-trigger-to-prevent-a-user-to-delete-a-table-in-the-database) and insert following records.

![Union and Union all Operator in sql](https://www.mindstick.com/blogs/1f3c746d-b99e-40a6-beb3-fbe88cab196a/images/d7a671da-9974-4e5a-912c-023f5eb2e36a.png)\

\

```
select * from Test.dbo.table_1unionselect * from Test.dbo.table_2
```

## output

\

![Union and Union all Operator in sql](https://www.mindstick.com/blogs/1f3c746d-b99e-40a6-beb3-fbe88cab196a/images/a5257b45-7e48-4ca3-8fd5-12f7e9836076.png)\

In the above example of union operator we have seen that union operator

\

result distinct records from the two or more [select statement](https://www.mindstick.com/forum/160076/explain-the-sql-select-statement-and-how-it-s-used-to-search-for-data-in-a-database). It eliminate

\

the repeated records from the result set. It Returns total 7 record

\
**Union All-**

Union All operator is use for [combining](https://yourviews.mindstick.com/view/85223/hybrid-vehicles-combining-fuel-efficiency-and-performance) the result set of two or more select

\

queries into [one result](https://answers.mindstick.com/qa/42846/what-was-one-result-of-the-haymarket-riot) set.

**Ex.**

```
select * from Test.dbo.table_1union allselect * from Test.dbo.table_2
```

\

![Union and Union all Operator in sql](https://www.mindstick.com/blogs/1f3c746d-b99e-40a6-beb3-fbe88cab196a/images/df203e73-4443-4d5b-bea6-cac3823d9ace.png)\

In the above example we have seen how to use union all operator. It returns

\

all the rows from the both table. It does not remove duplicate rows. It

\

returns total 8 records.

\

```
select Id,Email,Name from Test.dbo.table_1union allselect Id,Name,Email from Test.dbo.table_2
```

\

![Union and Union all Operator in sql](https://www.mindstick.com/blogs/1f3c746d-b99e-40a6-beb3-fbe88cab196a/images/2f0b6800-1e09-4c8e-9526-d6e2a61567e4.png)\

\

In the above statement the order of column is mismatching that’s why it

\

does not executed successfully so while selecting column we have to take

\

care of ordering. And in both the select statement column number should

\

have same.

\

```
select Id,Email from Test.dbo.table_1union allselect Id,Name,Email from Test.dbo.table_2
```

\

![Union and Union all Operator in sql](https://www.mindstick.com/blogs/1f3c746d-b99e-40a6-beb3-fbe88cab196a/images/7bebd2e6-d3ec-4b98-a699-cd8ee2e92c11.png)\

**The rules that we have to follow while using union**

**1.)** Number of columns and order of columns of all select statement must be same.

**2.)** The data types of the columns oftable in each statement must be same or compatible.

**3.)** It returned column names are taken from the first query.

UNION behaves like UNION [DISTINCT] , i.e. remove the duplicate rows; however,

using ALL keyword with UNION returns all rows, including duplicates.

## Difference between Union and Union all

1-Union eliminate repeated rows.

\

2-Union all does not remove duplicate rows.

\

**Difference between Sql Join and Union**

**1.)** The columns of joining tables may be different in JOIN but in UNION the number of columns and order of columns of all queries must be same.

**2.)** The UNION puts rows from queries after each other( puts vertically ) but JOIN puts the column from queries after each other (puts horizontally), i.e. it makes a cartesian product means if it has m row and n column then it returns m*n records.

**You can also visit these related useful link**

[Sql union operator](https://www.mindstick.com/forum/33565/sql-union-operator)

[Sql, Intersection and Except Operator in sql sever](https://www.mindstick.com/blog/144/union-intersection-and-except-operator-in-sql-server)

---

Original Source: https://www.mindstick.com/blog/11268/union-and-union-all-operator-in-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.