UNION and UNION ALL in SQL

The UNION operation in SQL is used to combine the result sets of two or more SELECT statements. An important thing to remember about UNION is that it automatically removes duplicate rows. In contrast, UNION ALL combines the result sets of two or more SELECT statements, but it doesn't remove duplicate rows, leading to faster performance in some cases.

Basic Syntax


                            SELECT column_name(s) FROM table1
                            UNION
                            SELECT column_name(s) FROM table2;
                            
                            SELECT column_name(s) FROM table1
                            UNION ALL
                            SELECT column_name(s) FROM table2;

Example

Let's assume we have two tables - Orders_USA and Orders_UK. We want to retrieve all unique order IDs from both tables using UNION, and then retrieve all order IDs including duplicates using UNION ALL. 


                            -- Using UNION
                            SELECT 
                                USA.OrderID, 
                                USA.OrderDate, 
                                USA.Amount, 
                                CustUSA.CustomerName 
                            FROM 
                                Orders_USA USA
                            JOIN 
                                Customers_USA CustUSA ON USA.CustomerID = CustUSA.CustomerID
                            WHERE 
                                USA.Amount > 500
                            
                            UNION
                            
                            SELECT 
                                UK.OrderID, 
                                UK.OrderDate, 
                                UK.Amount, 
                                CustUK.CustomerName 
                            FROM 
                                Orders_UK UK
                            JOIN 
                                Customers_UK CustUK ON UK.CustomerID = CustUK.CustomerID
                            WHERE 
                                UK.Amount > 500
                            
                            ORDER BY OrderDate;
                            
                            -- Using UNION ALL
                            SELECT 
                                USA.OrderID, 
                                USA.OrderDate, 
                                USA.Amount, 
                                CustUSA.CustomerName 
                            FROM 
                                Orders_USA USA
                            JOIN 
                                Customers_USA CustUSA ON USA.CustomerID = CustUSA.CustomerID
                            WHERE 
                                USA.Amount > 500
                            
                            UNION ALL
                            
                            SELECT 
                                UK.OrderID, 
                                UK.OrderDate, 
                                UK.Amount, 
                                CustUK.CustomerName 
                            FROM 
                                Orders_UK UK
                            JOIN 
                                Customers_UK CustUK ON UK.CustomerID = CustUK.CustomerID
                            WHERE 
                                UK.Amount > 500
                            
                            ORDER BY OrderDate;

The first query retrieves all unique order IDs from both tables, while the second query retrieves all order IDs, including duplicates.

Considerations

  • For UNION to work, each SELECT statement must have the same number of columns, the columns must be of compatible data types, and they should be in the same order.
  • UNION ALL is generally faster than UNION, because it doesn't need to check for duplicates. If you're certain there are no duplicates or if duplicates are acceptable, UNION ALL is preferable.
  • Always use the ORDER BY clause if you need the result set in a particular order. Note that the ORDER BY clause applies to the entire result, not just the individual SELECT statements.