SQL UNION Statement

Introduction

In this article, we will learn that the SQL Union statement combines the result sets of two or more Select statements into a single result set. It is a set operation that allows you to retrieve data from multiple tables or views and display it as a single result set.

The basic syntax for the union statement is as follows.

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;

In this example, two Select statements are combined using the Union operator. The result set will contain all the rows returned by both Select statements, with duplicate rows removed. It is important to note that the Select statements used with the union operator must have the same number of columns, and the data types of the corresponding columns must be compatible. The columns must also be in the same Order in both Select statements. The union operator can be used with other set operators, such as Intersect and except, to manipulate further and refine the result set.

Statement 1. SQL Union

In the SQL union command, the results of two or more select statements are combined (only distinct values). Without returning any duplicate rows, the SQL Union clause/operator is used to merge the results of two or more Select statements. Each Select operation within the Union must produce results with an identical amount of fields and similar data types.

Note

There must be the same number of expressions in both Select statements.
The corresponding expressions must have the same data type in the select statements.

Statement 2. Union, All operator

The Union all operator combines the result set of two or more select statements (allows duplicate values).

The following SQL statement returns the EmployeeName (duplicate values) from the EmployeeDetails and the EmployeeDetail table.

SELECT EmployeeName FROM EmployeeDetails    
UNION ALL    
SELECT EmpName FROM EmployeeDetail    
ORDER BY EmployeeName  ;

This query will return a result set that includes all employee names from both tables and orders them alphabetically by the EmployeeName column. If there are duplicate names, they will be included in the result set as the e Union; All operator returns all rows from both tables.

Output

The above query selects the column name EmployeeName with the table name "EmployeeDetails" and EmpName from EmployeeDetails according to EmployeeName.

Statement 3. Select a simple Union

The Select statement uses the result set, which includes the EmployeeName and EmpName columns of the EmployeeDetail table.

SELECT EmpName FROM EmployeeDetail      
UNION      
SELECT EmpName FROM  EmployeeDetail      
ORDER BY EmpName

The given SQL query retrieves all unique employee names from the "EmployeeDetail" table and then sorts them alphabetically in ascending Order. However, the query includes the same table twice, making it redundant. The correct query would be

SELECT DISTINCT EmpName FROM EmployeeDetail ORDER BY EmpName;

This will return a list of unique employee names in alphabetical Order. The Order by clause sorts the results in ascending Order based on the employee name. The Distinct keyword is used to ensure that unique values are returned.

Output

The above query selects the column name EmployeeName with the table name "EmployeeDetail" and adds the column EmpName and the table name EmployeeDetail and OrderByEmpName.

Statement 4. Select Into with Union

In the second statement, the Select into clause specifies that EmployeeDetail holds the final result set of the Union of the selected columns of the EmpName and EmployeeDetail tables. The NewEmployeeDetails table is created in the first Select statement.

SELECT EmployeeId, EmployeeName       
INTO NewEmployeeDetails     
FROM  EmployeeDetails      
WHERE EmployeeID NOT IN (2, 4)      
UNION      
SELECT EmployeeId, EmployeeName      
FROM EmployeeDetails    
GO     
Select * from NewEmployeeDetails

The above query selects EmployeeId, EmployeeName into NewImployeeDetails with the table name "EmployeeDetails" where EmployeeID NOT IN(2,4) and adds the column name EmployeeId, EmployeeName from the table name 'EmployeeDetails' and displays the contents of the table name 'NewEmployeeDetail. '.

Output

SQL UNION Statement

Statement 5. Select Union the of two select statements with OrderBy

Selecting the Union of two Select statements is the Order of certain parameters used with the Union clause, which 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.

SELECT Id, EmpName       
FROM EmployeeDetail     
WHERE Id NOT IN (4, 6)      
UNION      
SELECT EmployeeID, EmployeeName       
FROM EmployeeDetails    
ORDER BY EmpName ;

Output

Statement 6. Select Union of two Select statements to show the effects of All and parentheses

The Select Union is used to Union combine the results of three tables with the same five 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 five rows. The second Union is processed first because it's in parentheses and returnsfive5 rows. After all, the All option isn't used, and the duplicates are removed. These five rows are combined with the results of the first Seleclt by using the Union of all keywords. The final result has ten rows. This example doesn't remove the duplicates between the two sets of five rows. The Aboy select selects the name EpName, and EmpAddress from the table name.

Select Union of two Select statements with where and OrderBy

The following example Union shows the incorrect and correct Union in two Select statements where, Where and OrderBy are needed.

--INCORRECT     
SELECT Id, EmpName       
FROM EmployeeDetail     
WHERE Id = Id     
ORDER BY EmpName        
SELECT EmployeeID, EmployeeName       
FROM EmployeeDetails     
 --CORRECT     
SELECT Id, EmpName       
FROM EmployeeDetail     
WHERE Id = Id    
UNION      
SELECT EmployeeID, EmployeeName       
FROM EmployeeDetails     
ORDER BY EmpName

Output

SQL UNION Statement

Statement 7. Union of two Select statements to show the 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 two times. The second example uses Union without All to eliminate the duplicate rows from the combined results of the two Select statements and returns only the unduplicated rows from the source table. 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 d and duplicates are removed. These rows are combined with the results of the first Select by using the UnionAll keywords. This example doesn't remove the duplicates between the two sets.

Select EmployeeName, EmployeeAddress, EmployeeCity from EmployeeDetails Union All Select EmpName,EmpAddress,EmpCity from EmployeeDetail
Select EmployeeName,EmployeeAddress,EmployeeCity from EmployeeDetails

Output

SQL UNION Statement

The above query selects the column name EmployeeName, EmployeeAddress from the table name "EmployeeDetails" and selects all the row data from the column 'EmpNam,' 'EmpAddress' and 'EmpCity' from the table name EmployeeDetail and also selects the column 'EmployeeName' 'EmployeeAddress' and 'EmployeeCity' from the table EmployeeDetails.

Conclusion

This article taught us learned the basics of the SQL Select Union statement.

FAQs

Q. What are UNION, MINUS, and INTERSECT commands?

A. The UNION operator is used to combine the results of two tables while also removing duplicate entries.

Q. The MINUS operator returns rows from the first query but not from the second query.

A. The INTERSECT operator combines the results of both queries into a single row. Before running either of the above SQL statements, certain requirements must be satisfied –

Each SELECT query must have the same number of columns within the clause.
The data types in the columns must also be comparable.
The columns must be in the same Order in each SELECT statement.

Q. What is Cursor? How to use a Cursor?

A. After any variable declaration, Declare a cursor. A Select statement must always be coupled with the cursor definition. To start the result set, move the cursor over it. Before obtaining rows from the result set, the open statement must use the Fatch command touted. To retrieve and go to the next row in the command. To disable the cursor, use the CLOSE command. Finally, use the Deallocate command to remove the cursor definition and free up the resources connected with it.

Q. What is OLTP?

A. OLTP, or online transactional processing, allows huge groups of people to execute massive database transactional in real-time, usually via the Internet. A database transaction occurs when data is changed, inserted, deleted, or queried.

Q. What are the differences between OLTP and OLAP?

A. OLTP stands for online transaction processing, whereas OLAP stands for online analytical processing. OLTP is an online database modification system, whereas OLAP is an online database query response system.

Q. How to create empty tables with the same structure as another table?

A. To create empty tables: Using the into the operator to fetch the records of one table into a new table while setting a Where clause to false for all entries, it is possible to create empty tables with the same structure. As a result, SQL creates a new table with a duplicate structure to accept the fetched entries, but nothing is stored in the new table since the WHERE clause is active.