---
title: "Union Example in SQL Server"  
description: "In this article I am going to explain a use of union operator in SQL Server Database with a real life scenario and example."  
author: "Chris Anderson"  
published: 2014-10-08  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/1510/union-example-in-sql-server  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 6 minutes  

---

# Union Example in SQL Server

In this article I am going to explain a use of union [operator in SQL](https://www.mindstick.com/forum/160543/how-to-filter-records-using-like-operator-in-sql-server) [Server Database](https://www.mindstick.com/forum/159667/how-to-migrate-the-sql-server-database-to-a-lower-version) with a real life scenario and example.\

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each [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) with the UNOIN must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Before moving towards union query, let understand what is our scenario. Suppose we have three tables (Product, Customer and Order) in our database and we want to see the number of orders by [month name](https://www.mindstick.com/forum/323/find-the-day-name-and-month-name-from-current-date), week days and between three hours gaps in day.

Let’s move towards our example:

1-Product [table structure](https://answers.mindstick.com/qa/113649/what-is-the-database-table-structure-of-url-analyzer-details-to-store) and data. This table is not necessary to create for this example if you already have a ProductId in other table, to relate with Order table.

![Union Example in SQL Server](https://www.mindstick.com/mindstickarticle/adeb48fa-85cc-4671-8b2e-86adfb34406d/images/c13faa08-508c-40c5-85dc-4f05d6e4c66f.png)**\**

2-Customer table structure and data. This table is also not necessary to create for this example if you already have a CustomerId in other table, to relate with Order table.

![Union Example in SQL Server](https://www.mindstick.com/mindstickarticle/adeb48fa-85cc-4671-8b2e-86adfb34406d/images/0082fb00-6fe0-4765-a67f-20f3a206dff4.png)**\**

3-Order table structure and data. This table is necessary specially OrderDate column of this [table because](https://www.mindstick.com/forum/159042/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint-why) we are going to use OrderDate column in all our further queries which shows us use of union operator in real scenario.

![Union Example in SQL Server](https://www.mindstick.com/mindstickarticle/adeb48fa-85cc-4671-8b2e-86adfb34406d/images/bbfb9445-d59d-4f62-906d-7eb9c89a0559.png)\

4-First union query which show the orders (number of orders received by month) result by month name:

```
Select [Month], [Orders] From (
      Select 'January' as [Month], 1 as [MonthNum], COUNT(*) as [Orders]  From [Order] Where MONTH(OrderDate) = 1
            Union
      Select 'Febuary' as [Month], 2 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 2
            Union
      Select 'March' as [Month], 3 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 3
            Union
      Select 'April' as [Month], 4 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 4
            Union
      Select 'May' as [Month], 5 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 5
            Union
      Select 'June' as [Month], 6 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 6
            Union
      Select 'July' as [Month], 7 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 7
            Union
      Select 'August' as [Month], 8 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 8
            Union
      Select 'September' as [Month], 9 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 9
            Union
      Select 'October' as [Month], 10 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 10
            Union
      Select 'November' as [Month], 11 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 11
            Union
      Select 'December' as [Month], 12 as [MonthNum], COUNT(*) as [Orders] From [Order] Where MONTH(OrderDate) = 12
) Results Order by MonthNum
```

![Union Example in SQL Server](https://www.mindstick.com/mindstickarticle/adeb48fa-85cc-4671-8b2e-86adfb34406d/images/33169c80-5a5a-4dbb-8745-c36384851426.png)

Second union query which show the orders (number of orders received by week day) result by week day:

```
Select [DayofWeek], [Orders] From (
      Select 'Sunday' as [DayofWeek], 1 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 1
            Union
      Select 'Monday' as [DayofWeek], 2 as [WeekNum], COUNT(*) as [Orders]  From [Order] Where DATEPART(dw, OrderDate) = 2
            Union
      Select 'Tuesday' as [DayofWeek], 3 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 3
            Union
      Select 'Wednesday' as [DayofWeek], 4 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 4
            Union
      Select 'Thursday' as [DayofWeek], 5 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 5
            Union
      Select 'Friday' as [DayofWeek], 6 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 6
            Union
      Select 'Saturday' as [DayofWeek], 7 as [WeekNum], COUNT(*) as [Orders] From [Order] Where DATEPART(dw, OrderDate) = 7
) Results Order by [WeekNum]
```

![Union Example in SQL Server](https://www.mindstick.com/mindstickarticle/adeb48fa-85cc-4671-8b2e-86adfb34406d/images/66f37a7d-6898-475c-8f36-d96803423791.png)

Third union query which show the orders (number of orders received by gap of 3 hours in a day) result by gap of 3 hours in a day:

```
Select [TimeofDay], [Orders] From (
      Select '12 AM to 03 AM' as [TimeofDay], 1 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '00:00:00' and '03:00:00'
            Union
      Select '03 AM to 06 AM' as [TimeofDay], 2 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '03:00:00' and '06:00:00'
            Union
      Select '06 AM to 09 AM' as [TimeofDay], 3 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '06:00:00' and '09:00:00'
            Union
      Select '09 AM to 12 PM' as [TimeofDay], 4 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '09:00:00' and '12:00:00'
            Union
      Select '12 PM to 03 PM' as [TimeofDay], 5 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '12:00:00' and '15:00:00'
            Union
      Select '03 PM to 06 PM' as [TimeofDay], 6 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '15:00:00' and '18:00:00'
            Union
      Select '06 PM to 09 PM' as [TimeofDay], 7 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '18:00:00' and '21:00:00'
            Union
      Select '09 PM to 12 AM' as [TimeofDay], 8 as [TimeNum], COUNT(*) as [Orders] From [Order] Where cast(OrderDate as time) between '21:00:00' and '00:00:00'
) Results Order by [TimeNum]
```

![Union Example in SQL Server](https://www.mindstick.com/mindstickarticle/adeb48fa-85cc-4671-8b2e-86adfb34406d/images/1950668d-e15a-4a90-aa50-92e7bfb23f19.png)

Thanks for reading this article. [Please suggest](https://answers.mindstick.com/qa/43506/please-suggest-the-bathing-date-at-kumbh) me how can I improve this one and other [alternative](https://www.mindstick.com/forum/2324/what-is-the-alternative-to-the-mvc) way of doing the similar scenario.

---

Original Source: https://www.mindstick.com/articles/1510/union-example-in-sql-server

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