SQL UNION Statement

SQL UNION operator

 
The SQL UNION  command combines the result set of two, or more SELECT statements (only distinct values).
 
The SQL UNION clause/operator is used to combine the results of two, or more SELECT statements without returning any duplicate rows.
 
Each SELECT statement, within the UNION ALL, must have the same number of fields in the result sets, with 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 
  • See also the UNION operator
Syntax
  1. SELECT EmployeeName FROM EmployeeDetails    
  2. UNION    
  3. SELECT EmpName FROM  EmployeeDetail    
  4. ORDER BY EmployeeName    
  Example
 
 

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 also) from both the EmployeeDetails and the EmployeeDetail table
 
Syntax
  1. SELECT EmployeeName FROM EmployeeDetails    
  2. UNION ALL    
  3. SELECT EmpName FROM EmployeeDetail    
  4. ORDER BY EmployeeName  ;   
 Example 
 

SELECT a simple UNION statement 

 
The SELECT statement uses the result set includes the contents of the EmployeeName  and EmpName columns, of both the And EmployeeDetails table
  1. SELECT EmployeeName FROM EmployeeDetails      
  2. UNION      
  3. SELECT EmpName FROM  EmployeeDetail      
  4. ORDER BY EmployeeName      
Example 
 

SELECT INTO with UNION statement

 
The SELECT INTO the clause, in the second the statement, specifies that the table named EmployeeDetails, holds the final result set of the union of the selected columns of the EmpName and EmployeeDetail tables.  The NewCarSale table is created in the first SELECT statement
 
Syntax
  1. SELECT EmployeeId, EmployeeName       
  2. INTO NewCarSale     
  3. FROM  EmployeeDetails      
  4. WHERE EmployeeID NOT IN (2, 4)      
  5. UNION      
  6. SELECT EmployeeId, EmployeeName      
  7. FROM EmployeeDetails    
  8. GO     
  9. Select * from NewCarSale    
Example 
 
 

SELECT  UNION of two SELECT statements with ORDER BY

 
Select the UNION of two SELECT statements is 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
 
Syntax
  1. SELECT EmpId, EmpName       
  2. FROM EmployeeDetail     
  3. WHERE EmpId NOT IN (4, 6)      
  4. UNION      
  5. SELECT EmployeeID, EmployeeName       
  6. FROM EmployeeDetails    
  7. ORDER BY EmpName ;      
  8. GO    
 

Select  UNION of two SELECT statements to show the effects of ALL and parentheses

 
The Select UNION is used 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 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.
 
Example
  1. SELECT  EmpName,EmpAddress        
  2. INTO CarSal      
  3. FROM  EmployeeDetail AS pp JOIN EmployeeDetails AS e      
  4. ON EmployeeID =  EmployeeID      
  5. WHERE EmployeeName = 'Rahul';      
  6. GO      
  7. SELECT EmpName, EmpAddress       
  8. INTO NewCarSale      
  9. FROM  EmployeeDetails AS pp JOIN EmployeeDetail AS e      
  10. ON EmployeeID =  EmployeeID      
  11. WHERE EmployeeName = 'Rahul';      
  12. GO      
  13.     
  14.     
  15. -- Union ALL      
  16. SELECT EmployeeName, EmployeeAddress      
  17. FROM  EmployeeDetails      
  18. UNION ALL      
  19. SELECT EmpName, EmpAddress      
  20. FROM EmployeeDetail      
  21. GO      
  22.       
  23. SELECT EmployeeName, EmployeeAddress      
  24. FROM  EmployeeDetails      
  25. UNION      
  26. SELECT EmpName, EmpAddress      
  27. FROM EmployeeDetail      
  28. GO      
  29.     
  30. SELECT EmployeeName, EmployeeAddress      
  31. FROM  EmployeeDetails      
  32. UNION ALL      
  33. (    
  34. SELECT EmpName, EmpAddress      
  35. FROM EmployeeDetail      
  36. )    
  37. GO    

SELECT 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. 
 
Syntax
  1. --INCORRECT     
  2. SELECT EmpId, EmpName       
  3. FROM EmployeeDetail     
  4. WHERE EmpId = EmpId     
  5. ORDER BY EmpName    
  6. UNION      
  7. SELECT EmployeeID, EmployeeName       
  8. FROM EmployeeDetails     
  9.  --CORRECT     
  10. SELECT EmpId, EmpName       
  11. FROM EmployeeDetail     
  12. WHERE EmpId = EmpId    
  13. UNION      
  14. SELECT EmployeeID, EmployeeName       
  15. FROM EmployeeDetails     
  16. ORDER BY EmpName   
Example 
 
 

Using 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 firs,t 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.
 
Syntax
  1. SELECT EmployeeName, EmployeeAddress, EmployeeCity      
  2. FROM EmployeeDetails      
  3. UNION ALL       
  4. SELECT EmpName, EmpAddress, EmpCity       
  5. FROM EmployeeDetail         
  6. SELECT EmployeeName, EmployeeAddress, EmployeeCity      
  7. FROM EmployeeDetails      
  8. UNION      
  9. SELECT EmpName, EmpAddress, EmpCity       
  10. FROM EmployeeDetail     
Example
 
 

SUMMARY

 
In this tutorial, we learned the basics of the SQL SELECT UNION  statement.