Combining Results with UNION and UNION ALL in SQL

Table of Contents

1. Introduction
2. What is UNION in SQL?
3. When to Use UNION
4. Basic Syntax of UNION
5. What is UNION ALL?
6. Difference Between UNION and UNION ALL
7. Requirements for Using UNION
8. Matching Columns and Data Types
9. Using Aliases in UNION Queries
10. UNION with Literal Data
11. Ordering the Final Output
12. Filtering Results in UNION
13. UNION with Different Table Sources
14. Real-World Example: Employees and Contractors
15. Performance Comparison: UNION vs UNION ALL
16. Using DISTINCT with UNION ALL
17. Combining UNION with Joins or Subqueries
18. Common Mistakes with UNION
19. Best Practices for UNION Usage
20. Summary and What’s Next

1. Introduction

In SQL, there are situations where you need to combine results from multiple queries. Whether it’s data coming from different tables or different sources, UNION and UNION ALL help you stitch them together into a single result set.

2. What is UNION in SQL?

UNION is a SQL operator used to combine the result sets of two or more SELECT statements, removing duplicate rows.

3. When to Use UNION

• To merge results from multiple tables with similar structure
• To show combined data (e.g., full employee list from different departments)
• To perform reporting or data consolidation tasks
• To avoid multiple queries in application code

4. Basic Syntax of UNION

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

5. What is UNION ALL?

UNION ALL works like UNION but does not eliminate duplicate rows. It returns all records, including duplicates.

sqlCopyEditSELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

6. Difference Between UNION and UNION ALL

7. Requirements for Using UNION

• Number of columns in all SELECT statements must match
• Data types must be compatible (e.g., INT with INT, VARCHAR with VARCHAR)
• Order of columns matters

8. Matching Columns and Data Types

Valid Example:

sqlCopyEditSELECT id, name FROM employees
UNION
SELECT id, name FROM contractors;

Invalid Example (throws error):

sqlCopyEditSELECT id, name FROM employees
UNION
SELECT salary, hire_date FROM contractors;

Column meanings must align across queries.

9. Using Aliases in UNION Queries

Only the first SELECT query’s aliases are retained in the final result:

sqlCopyEditSELECT name AS full_name FROM employees
UNION
SELECT name FROM contractors;

Result column will be full_name.

10. UNION with Literal Data

sqlCopyEditSELECT 'employee' AS type, name FROM employees
UNION
SELECT 'contractor', name FROM contractors;

Adds a column to indicate data origin.

11. Ordering the Final Output

You must use ORDER BY after the last SELECT:

sqlCopyEditSELECT name FROM employees
UNION
SELECT name FROM contractors
ORDER BY name;

You cannot use ORDER BY inside each individual SELECT.

12. Filtering Results in UNION

Use WHERE clauses in individual queries:

sqlCopyEditSELECT name FROM employees WHERE status = 'active'
UNION
SELECT name FROM contractors WHERE status = 'active';

Filters are applied before combining results.

13. UNION with Different Table Sources

Even if table structures differ, you can align columns using SELECT:

sqlCopyEditSELECT id, name, NULL AS rate FROM employees
UNION
SELECT id, NULL, hourly_rate FROM freelancers;

Use NULL or constants to fill missing values.

14. Real-World Example: Employees and Contractors

sqlCopyEditSELECT id, name, 'Employee' AS role FROM employees
UNION
SELECT id, name, 'Contractor' FROM contractors;

Combines people from both tables and labels their source.

15. Performance Comparison: UNION vs UNION ALL

16. Using DISTINCT with UNION ALL

You can simulate UNION using DISTINCT:

sqlCopyEditSELECT DISTINCT * FROM (
  SELECT name FROM table1
  UNION ALL
  SELECT name FROM table2
) AS combined;

Sometimes useful when applying DISTINCT after a transformation.

17. Combining UNION with Joins or Subqueries

sqlCopyEditSELECT u.id, u.name FROM users u
JOIN orders o ON u.id = o.user_id
UNION
SELECT id, name FROM archived_users;

You can UNION a join result with another query as long as the columns align.

18. Common Mistakes with UNION

19. Best Practices for UNION Usage

• Use UNION ALL when performance matters and duplicates don’t
• Always align column order and data types
• Use column aliases in the first SELECT
• Comment each SELECT for readability if complex
• Use subqueries to preprocess before UNION if needed

20. Summary and What’s Next

UNION and UNION ALL are powerful SQL tools for combining multiple datasets into a single result set. Use UNION for uniqueness and UNION ALL for speed and completeness. Mastering these helps you write efficient and expressive SQL for reporting, analytics, and data integration.