WikiGalaxy
SQL Union
Overview:
The SQL UNION operator is used to combine the result sets of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types.
Key Features:
UNION removes duplicate records by default. If you want to include duplicates, use UNION ALL.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Console Output Example:
Result Set with Unique Records
Example 1: Combining Two Tables
Scenario:
Combine employee names from two departments.
SELECT name FROM department_a
UNION
SELECT name FROM department_b;
Console Output Example:
Unique Employee Names
Example 2: Using UNION ALL
Scenario:
Combine sales data including duplicates.
SELECT sale_amount FROM sales_2022
UNION ALL
SELECT sale_amount FROM sales_2023;
Console Output Example:
All Sale Amounts Including Duplicates
Example 3: Different Column Names
Scenario:
Combine product IDs from different tables with different column names.
SELECT product_id FROM products_2022
UNION
SELECT id FROM products_2023;
Console Output Example:
Unique Product IDs
Example 4: UNION with WHERE Clause
Scenario:
Combine customer data where age is greater than 30.
SELECT customer_name FROM customers_usa WHERE age > 30
UNION
SELECT customer_name FROM customers_canada WHERE age > 30;
Console Output Example:
Unique Customer Names over 30
Example 5: UNION with ORDER BY
Scenario:
Combine and sort employee IDs.
SELECT employee_id FROM employees_2022
UNION
SELECT employee_id FROM employees_2023
ORDER BY employee_id;
Console Output Example:
Sorted Unique Employee IDs
Example 6: UNION with NULL Values
Scenario:
Combine records with possible NULL values.
SELECT city FROM addresses_2022
UNION
SELECT city FROM addresses_2023;
Console Output Example:
Unique Cities Including NULL
Example 7: UNION with Different Data Types
Scenario:
Combine data from tables with compatible data types.
SELECT CAST(id AS VARCHAR) FROM table1
UNION
SELECT name FROM table2;
Console Output Example:
Combined IDs and Names
Example 8: UNION with Complex Queries
Scenario:
Use UNION in complex queries involving multiple conditions.
SELECT customer_id FROM orders WHERE status = 'completed'
UNION
SELECT customer_id FROM returns WHERE return_reason = 'defective';
Console Output Example:
Unique Customer IDs from Orders and Returns
