How to use union in view SQL Server

In this SQL Server tutorial, we will learn how to use union in view SQL Server. Moreover, we will also cover the following topics.

• Union in SQL Server
• View in SQL Server
• How to use union in view SQL Server
• How to use union all in view SQL Server

Before we cover the use of union or union all in a SQL Server View, we should have a clear understanding of the UNION operator and a View in SQL Server.

View in SQL Server

In SQL Server, a VIEW is just like a virtual table that holds data from one or more than one table. A view includes a set of SQL queries for retrieving data from the database. For more information, refer to “View in SQL Server“.

In this section, we will only cover the syntax of creating a view in SQL Server. And the syntax is as follows.

/* Syntax of creating a view */
CREATE VIEW view_name AS
SELECT expressions

FROM table_name

[WHERE condition];

• In the syntax, we are using the CREATE VIEW statement to create a simple user-defined view in SQL Server.
• Now, after CREATE VIEW keyword, we have to specify the name of the view instead of view_name. And then, we are using the AS keyword.
• After this, we can use the SELECT statement to fetch some columns from a table. Moreover, we can also specify any sort of condition with the SELECT statement.

Read Create a table from view in SQL Server

Union & Union All in SQL Server

In SQL Server, the UNION operator is used to perform a set operation of combining the results of multiple SELECT queries. As the name specifies, the main aim of the UNION operator is to perform the UNION operation.

Now, there is a slight difference between a UNION and a UNION ALL operator in SQL Server. A UNION operator in SQL Server does not include duplicate entries, while the UNION ALL operator includes duplicates in the result.

Let’s understand the syntax of using the UNION and UNION ALL operator in SQL Server.

/* Syntax of using UNION */
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

/* Syntax of using UNION ALL */
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Both UNION and UNION ALL operators are used between 2 or more SELECT statements. Moreover, the specifications of the columns in a UNION operation do not have to be identical. But, they must be compatible via implicit conversion.

To understand this operator better, let’s, take a simple example related to both the operators. And for this, consider the following tables.

Now, we will use both the tables given below to illustrate examples related to the use of UNION and UNION ALL operators.

USE [sqlserverguides]
GO

SELECT City, Country FROM dbo.Customers_1
UNION
SELECT City, Country FROM dbo.Customers_2

USE [sqlserverguides]
GO

SELECT City, Country FROM dbo.Customers_1
UNION ALL
SELECT City, Country FROM dbo.Customers_2

In both the above queries, we are using the SELECT statement to fetch City and Country columns from the tables. Now, in one query, we have used the UNION operator and in the other, we have used the UNION ALL. However, here are the results from the respective queries.

Now, from the above outputs, we can easily understand the difference between UNION and UNION ALL operators.

Read SQL Server view order by

How to use union in view SQL Server

Now that we have a clear idea about the use of the UNION operator, let’s understand how to use it in a view in SQL Server.

So, using a UNION operator in a SQL Server VIEW is quite simple. Generally, a simple view in SQL Server includes the use of a single select statement. But, with the help of the UNION operator, we can combine the results of multiple SELECT statements within a single view.

Let’s understand the whole implementation using an example. And again for this example, we will use the same tables illustrated in the previous section.

USE [sqlserverguides]
GO

CREATE VIEW US_CityView
AS
SELECT City, Country FROM dbo.Customers_1
WHERE Country = 'United States'
UNION
SELECT City, Country FROM dbo.Customers_2
WHERE Country = 'United States';
GO

In the above example, we have created a view with the name US_CityView. And in the definition, we are using 2 SELECT statements to fetch the city and country columns from respective tables.

Moreover, we are also using a WHERE clause with each SELECT statement to fetch records where the country is the United States.

So, the view will return the distinct city names from both the tables that come under the United States.

Read How to view table in SQL Server

How to use union all in view SQL Server

Now that we have a clear idea of how to use the UNION operator in a SQL Server View, let’s understand how to use the UNION ALL operator in a view.

Similar to UNION operator in a SQL Server VIEW, using UNION ALL is also quite simple. Generally, a simple view in SQL Server includes the use of a single select statement. But, with the help of the UNION ALL operator, we can even combine duplicate entries of multiple SELECT statements within a single view.

Let’s understand the whole implementation using an example. And again for this example, we will use the same tables illustrated in the previous section.

USE [sqlserverguides]
GO

CREATE VIEW CityView_CA
AS
SELECT Name, City, Country FROM dbo.Customers_1
WHERE Country = 'Canada'
UNION ALL
SELECT Name, City, Country FROM dbo.Customers_2
WHERE Country = 'Canada';
GO

In the above example, we have created a view with the name CityView_CA. And in the definition, we are using 2 SELECT statements to fetch the name, city, and country columns from respective tables.

Moreover, we are also using a WHERE clause with each SELECT statement to fetch records where the country is Canada. And to combine the results, we are using the UNION ALL operator between the SELECT statements.

You may like the following SQL Server tutorials:

• How to see view definition in SQL Server
• View SQL Server Error Logs
• SQL Server String_agg
• How to call a view in SQL Server
• SQL Server Datetime functions examples
• SQL Server logical operators and example
• SQL Server bulk insert from CSV file
• SQL Server function return table

So, in this tutorial, we have learned how to use union in view SQL Server. Moreover, we have also covered the following topics.

• Union in SQL Server
• View in SQL Server
• How to use union in view SQL Server
• How to use union all in view SQL Server

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.