- Article
Applies to: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Concatenates the results of two queries into a single result set. You control whether the result set includes duplicate rows:
- UNION ALL - Includes duplicates.
- UNION - Excludes duplicates.
A UNION operation is different from a JOIN:
- A UNION concatenates result sets from two queries. But a UNION does not create individual rows from columns gathered from two tables.
- A JOIN compares columns from two tables, to create result rows composed of columns from two tables.
The following are basic rules for combining the result sets of two queries by using UNION:
The number and the order of the columns must be the same in all queries.
The data types must be compatible.
Transact-SQL syntax conventions
Syntax
{ <query_specification> | ( <query_expression> ) } { UNION [ ALL ] { <query_specification> | ( <query_expression> ) } [ ...n ] }Note
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
<query_specification> | ( <query_expression> )Is a query specification or query expression that returns data to be combined with the data from another query specification or query expression. The definitions of the columns that are part of a UNION operation don't have to be the same, but they must be compatible through implicit conversion. When data types differ, the resulting data type is determined based on the rules for data type precedence. When the types are the same but differ in precision, scale, or length, the result is based on the same rules for combining expressions. For more information, see Precision, Scale, and Length (Transact-SQL).
Columns of the xml data type must be equal. All columns must be either typed to an XML schema or untyped. If typed, they must be typed to the same XML schema collection.
UNION
Specifies that multiple result sets are to be combined and returned as a single result set.
ALL
Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.
Examples
A. Using a simple UNION
In the following example, the result set includes the contents of the ProductModelID and Name columns of both the ProductModel and Gloves tables.
-- Uses AdventureWorks IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO -- Here is the simple union. -- Uses AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO B. Using SELECT INTO with UNION
In the following example, the INTO clause in the second SELECT statement specifies that the table named ProductResults holds the final result set of the union of the selected columns of the ProductModel and Gloves tables. The Gloves table is created in the first SELECT statement.
-- Uses AdventureWorks IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL DROP TABLE dbo.ProductResults; GO IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO -- Uses AdventureWorks SELECT ProductModelID, Name INTO dbo.ProductResults FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO SELECT ProductModelID, Name FROM dbo.ProductResults; C. Using UNION of two SELECT statements with ORDER BY
The order of certain parameters used with the UNION clause is important. The following example shows the incorrect and correct use of UNION in two SELECT statements in which a column is to be renamed in the output.
-- Uses AdventureWorks IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO /* INCORRECT */ -- Uses AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) ORDER BY Name UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO /* CORRECT */ -- Uses AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO D. Using UNION of three SELECT statements to show the effects of ALL and parentheses
The following examples use UNION to combine the results of three tables that all have the same 5 rows of data. The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements, and returns 5 rows.
The third example uses ALL with the first UNION and parentheses enclose the second UNION that isn't using ALL. The second UNION is processed first because it's in parentheses, and returns 5 rows because the ALL option isn't used and the duplicates are removed. These 5 rows are combined with the results of the first SELECT by using the UNION ALL keywords. This example doesn't remove the duplicates between the two sets of five rows. The final result has 10 rows.
-- Uses AdventureWorks IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL DROP TABLE dbo.EmployeeOne; GO IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL DROP TABLE dbo.EmployeeTwo; GO IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL DROP TABLE dbo.EmployeeThree; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeOne FROM Person.Person AS pp JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeTwo FROM Person.Person AS pp JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeThree FROM Person.Person AS pp JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO -- Union ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION ALL SELECT LastName, FirstName ,JobTitle FROM dbo.EmployeeTwo UNION ALL SELECT LastName, FirstName,JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName,JobTitle FROM dbo.EmployeeOne UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName,JobTitle FROM dbo.EmployeeOne UNION ALL ( SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree ); GO Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
E. Using a simple UNION
In the following example, the result set includes the contents of the CustomerKey columns of both the FactInternetSales and DimCustomer tables. Since the ALL keyword isn't used, duplicates are excluded from the results.
-- Uses AdventureWorks SELECT CustomerKey FROM FactInternetSales UNION SELECT CustomerKey FROM DimCustomer ORDER BY CustomerKey; F. Using UNION of two SELECT statements with ORDER BY
When any SELECT statement in a UNION statement includes an ORDER BY clause, that clause should be placed after all SELECT statements. The following example shows the incorrect and correct use of UNION in two SELECT statements in which a column is ordered with ORDER BY.
-- Uses AdventureWorks -- INCORRECT SELECT CustomerKey FROM FactInternetSales ORDER BY CustomerKey UNION SELECT CustomerKey FROM DimCustomer ORDER BY CustomerKey; -- CORRECT USE AdventureWorksPDW2012; SELECT CustomerKey FROM FactInternetSales UNION SELECT CustomerKey FROM DimCustomer ORDER BY CustomerKey; G. Using UNION of two SELECT statements with WHERE and ORDER BY
The following example shows the incorrect and correct use of UNION in two SELECT statements where WHERE and ORDER BY are needed.
-- Uses AdventureWorks -- INCORRECT SELECT CustomerKey FROM FactInternetSales WHERE CustomerKey >= 11000 ORDER BY CustomerKey UNION SELECT CustomerKey FROM DimCustomer ORDER BY CustomerKey; -- CORRECT USE AdventureWorksPDW2012; SELECT CustomerKey FROM FactInternetSales WHERE CustomerKey >= 11000 UNION SELECT CustomerKey FROM DimCustomer ORDER BY CustomerKey; H. Using UNION of three SELECT statements to show effects of ALL and parentheses
The following examples use UNION to combine the results of the same table to demonstrate the effects of ALL and parentheses when using UNION.
The first example uses UNION ALL to show duplicated records and returns each row in the source table three times. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements and returns only the unduplicated rows from the source table.
The third example uses ALL with the first UNION and parentheses enclosing the second UNION that isn't using ALL. The second UNION is processed first because it is in parentheses. It returns only the unduplicated rows from the table because the ALL option isn't used and duplicates are removed. These rows are combined with the results of the first SELECT by using the UNION ALL keywords. This example doesn't remove the duplicates between the two sets.
-- Uses AdventureWorks SELECT CustomerKey, FirstName, LastName FROM DimCustomer UNION ALL SELECT CustomerKey, FirstName, LastName FROM DimCustomer UNION ALL SELECT CustomerKey, FirstName, LastName FROM DimCustomer; SELECT CustomerKey, FirstName, LastName FROM DimCustomer UNION SELECT CustomerKey, FirstName, LastName FROM DimCustomer UNION SELECT CustomerKey, FirstName, LastName FROM DimCustomer; SELECT CustomerKey, FirstName, LastName FROM DimCustomer UNION ALL ( SELECT CustomerKey, FirstName, LastName FROM DimCustomer UNION SELECT CustomerKey, FirstName, LastName FROM DimCustomer ); See Also
SELECT (Transact-SQL)
SELECT Examples (Transact-SQL)
FAQs
What is the difference between UNION and UNION all in Transact SQL? ›
Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates. UNION performs a DISTINCT on the result set, eliminating any duplicate rows. UNION ALL does not remove duplicates, and it therefore faster than UNION.
What is UNION in T-SQL? ›What Is UNION in SQL? The UNION operator is used to combine the data from the result of two or more SELECT command queries into a single distinct result set. This operator removes any duplicates present in the results being combined.
How to use UNION query in SQL Server? ›- Every SELECT statement within UNION must have the same number of columns.
- The columns must also have similar data types.
- The columns in every SELECT statement must also be in the same order.
- Use UNION ALL.
- Execute each SQL separately and merge and sort the result sets within your program! ...
- Join the tables. ...
- In versions, 10g and beyond, explore the MODEL clause.
- Use a scalar subquery.
If you're wondering which variant to use, remember: Using UNION effectively performs a SELECT DISTINCT on the results set. If you know that all of the records returned by UNION are going to be unique, use UNION ALL ; it will be faster.
What is the benefit of UNION all in SQL? ›The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).
Is UNION better than join? ›There is a major difference between JOIN and UNION in SQL. Using the JOIN clause, we combine the attributes of two given relations and, as a result, form tuples. Whereas we use the UNION clause when we want to combine the results obtained from two queries.
Does UNION get rid of duplicates? ›In SQL, the UNION clause combines the results of two or more SELECT statements into a distinct single result set without returning any duplicate rows. In other words, SQL UNION operator concatenates the results of two queries and removes duplicate values.
How to join 2 query results in SQL? ›- On the Create tab, in the Queries group, click Query Design.
- On the Design tab, in the Query group, click Union. ...
- Click the tab for the first select query that you want to combine in the union query.
The SQL JOIN is used when we have to extract data from more than one table. The SQL UNION is used when we have to display the results of two or more SELECT statements. In the case of SQL JOINS, the records are combined into new columns. In the case of SQL UNION, the records are combined into new rows.
Does UNION match columns in SQL? ›
The SQL UNION operator
SQL has strict rules for appending data: Both tables must have the same number of columns. The columns must have the same data types in the same order as the first table.
UNION – Syntax
SELECT column_name1, coumn_name2, column3, column_name4 FROM table1 UNION SELECT column_name1, coumn_name2, column3, column_name4 FROM table2; UNION SELECT column_name1, coumn_name2, column3, column_name4 FROM table3; The above syntax is only for understanding how to combine more than two tables.
There are four basic Set Operators in SQL Server: Union. Union All.
Is UNION an expensive operation? ›UNION performs expensive distinct SORT operation which reduces the performances.
Is UNION costly SQL? ›If you compare the actual execution plan for both the statements, you can notice that statement with just UNION will be the costliest. This is because of the Distinct Sort operation. Distinct sort is an expensive operation. You cannot ignore UNION just because it's expensive than UNION ALL.
Do unions slow down SQL? ›A Union combines the results of two or more queries, however a UNION also verifies if there are duplicate values and removes them in the query results. If you did not know, that aspect can slow down a query. If possible, we always try to avoid it. That is why UNION ALL is faster.
When should we use union all? ›The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned). Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.
What is the maximum number of union all in SQL? ›The maximum number of subqueries within a single side of a union is 50. The total number of tables that can appear on all sides of a union query is 256.
What is the opposite of union all in SQL? ›UNION ALL: Combine two or more result sets into a single set, including all duplicates. INTERSECT: Takes the data from both result sets which are in common. EXCEPT: Takes the data from the first result set, but not in the second result set (i.e. no matching to each other)
What is the downside of union? ›Labor unions charge dues to pay the salaries of union leaders and workers during a strike. And unfortunately, some unions spend union dues on six-figure salaries for leaders and luxurious headquarters. Other drawbacks of labor union membership include less autonomy, workplace tension, and slower advancement.
What are the pros and cons of a union? ›
Labor unions benefit their members by negotiating better pay, benefits, and working conditions. Critics maintain that union contracts make it more difficult for a company to fire unproductive employees, and that they increase long-term costs which decreases competitiveness.
What are the advantages disadvantages of joining a union? ›Although union jobs often have increased competition, they are generally beneficial to employees. They generally offer higher wages, better healthcare, pensions, better working conditions, and more. That said, employees who join labor parties must pay regular fees to maintain their membership.
How long do you have to keep union records? ›The president and treasurer, or the corresponding principal officers, must ensure that unions maintain adequate records. How long do I have to keep these records? Records must be retained for 5 years after a report is filed.
Can you reverse a union? ›When employees no longer want to be represented by a union or want to replace the union with a different one, they can vote to decertify the union. The process to decertify a union starts with filing an RD petition at the regional National Labor Relations Board (NLRB) office or electronically on the NLRB website.
Can we have unequal columns in union? ›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.
How to join two tables in SQL without join? ›Syntax. Both UNION and UNION ALL are used to glue together the result of the first query with that of the second query. You simply write two SELECT statements and write UNION or UNION ALL between them.
What is the difference between join and inner join? ›Difference between JOIN and INNER JOIN
JOIN returns all rows from tables where the key record of one table is equal to the key records of another table. The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.
- Simple Join. First, all the tables are joined using the JOIN keyword, then the WHERE clause is used: FROM Employee e JOIN Salary s JOIN Department d. WHERE e. ID = s. Emp_ID AND e. ...
- Nested Join. The nested JOIN statement is used with the ON keyword: SELECT e. ID, e. Name, s. Salary, d.
In case there are a large number of rows in the tables and there is an index to use, INNER JOIN is generally faster than OUTER JOIN.
What is the best reason to use a relationship instead of a join? ›Relationships are a dynamic, flexible way to combine data from multiple tables for analysis. We recommend using relationships as your first approach to combining your data because it makes data preparation and analysis easier and more intuitive. Use joins only when you absolutely need to(Link opens in a new window).
What is the real purpose of a union type? ›
The primary use of a union is allowing access to a common location by different data types, for example hardware input/output access, bitfield and word sharing, or type punning. Unions can also provide low-level polymorphism.
Does UNION add rows or columns? ›A UNION concatenates result sets from two queries. But a UNION does not create individual rows from columns gathered from two tables. A JOIN compares columns from two tables, to create result rows composed of columns from two tables.
Does UNION change the order in SQL? ›UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned).
What is the best way to merge tables in SQL? ›The simplest way to combine two tables together is using the keywords UNION or UNION ALL. These two methods pile one lot of selected data on top of the other. The difference between the two keywords is that UNION only takes distinct values, but UNION ALL keeps all of the values selected.
What is the difference between UNION and distinct? ›The UNION operator is normally used to combine data from related tables that have not been normalized perfectly. UNION DISTINCT operator is used for combining DISTINCT result sets from more than one SELECT statement into one result set.
Can we use UNION for two different tables? ›The UNION (ALL) BY NAME clause can be used to combine rows from different tables by name, instead of by position. UNION BY NAME does not require both queries to have the same number of columns. Any columns that are only found in one of the queries are filled with NULL values for the other query.
Does column order matter in UNION SQL? ›UNION or UNION ALL have the same basic requirements of the data being combined: There must be the same number of columns retrieved in each SELECT statement to be combined. The columns retrieved must be in the same order in each SELECT statement.
Does UNION require same column names? ›Rules for using UNION
Corresponding columns in select statements merged by UNION do not need to have the same name. Because the names of the interleaved columns are likely to be different, do not use a column name after an ORDER BY. Instead, always use a column number, such as ORDER BY 1.
A Structure does not have a shared location for all of its members. It makes the size of a Structure to be greater than or equal to the sum of the size of its data members. A Union does not have a separate location for every member in it.
What is the main difference between UNION and UNION all? ›| UNION | UNION ALL |
|---|---|
| It combines the result set from multiple tables and returns distinct records into a single result set. | It combines the result set from multiple tables and returns all records into a single result set. |
What is the difference between UNION and Unionall? ›
UNION and UNION ALL in SQL are used to retrieve data from two or more tables. UNION returns distinct records from both the table, while UNION ALL returns all the records from both the tables.
What is UNION and UNION all function in SQL? ›| Union | Union All |
|---|---|
| It combines the result set from multiple tables with eliminating the duplicate records | It combines the result set from multiple tables without eliminating the duplicate records |
| It performs a distinct on the result set. | It does not perform distinct on the result set |
The main difference is that Union All doesn't require that the data sources are sorted, nor does its output. Besides, Union All accepts more than two inputs while Merge transformation doesn't. Before illustrating how to use the Merge transformation, we will explain several approaches to sort data sources in SSIS.
Will union remove duplicates? ›In SQL, the UNION clause combines the results of two or more SELECT statements into a distinct single result set without returning any duplicate rows. In other words, SQL UNION operator concatenates the results of two queries and removes duplicate values.
Why would you use union all? ›Both UNION and UNION ALL are clauses used to combine multiple queries into one result set. UNION will remove duplicates, while UNION ALL will not. UNION ALL runs faster because of this.
Does union all maintain order? ›As per standard SQL UNION / UNION ALL do not guarantee any particular sort order without an outer ORDER BY clause - like there is hardly any place in SQL where sort order is guaranteed without ORDER BY .
How to combine 3 tables in SQL using union? ›UNION – Syntax
SELECT column_name1, coumn_name2, column3, column_name4 FROM table1 UNION SELECT column_name1, coumn_name2, column3, column_name4 FROM table2; UNION SELECT column_name1, coumn_name2, column3, column_name4 FROM table3; The above syntax is only for understanding how to combine more than two tables.
Do union workers get better benefits? Yes. Union workers are more likely than their non-union counterparts to have access to health care and pension benefits.
How many types of union are there in SQL? ›There are four basic Set Operators in SQL Server: Union. Union All.
What is the difference between INTERSECT and inner join? ›Intersect is an operator while Inner join is not an operator but a type of join. Inner join does not consider Null values while Intersect operator does match Nulls. The intersect operator does not return any duplicate value while Inner joins return NULL values if it is available.
How to remove duplicates in SQL? ›
One of the easiest ways to remove duplicate data in SQL is by using the DISTINCT keyword. You can use the DISTINCT keyword in a SELECT statement to retrieve only unique values from a particular column.
Are unions better than joins? ›Union will be faster, as it simply passes the first SELECT statement, and then parses the second SELECT statement and adds the results to the end of the output table.
Are joins and unions the same? ›There is a major difference between JOIN and UNION in SQL. Using the JOIN clause, we combine the attributes of two given relations and, as a result, form tuples. Whereas we use the UNION clause when we want to combine the results obtained from two queries. They both combine data differently.
How to identify duplicates in SQL? ›To find the duplicate records, use GROUP BY and the COUNT keyword. To obtain all the duplicate records use inner join along with GROUP BY and COUNT keywords.