PostgreSQL UNION: Syntax, Examples, and Best Practices

PostgreSQL UNION: Combining Query Results

The UNION operator in PostgreSQL combines the results of two or more SELECT queries into a single dataset. By default, it removes duplicate rows. If you want to include duplicates, you can use UNION ALL.

Syntax for Using UNION

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

Key Points:

1. The number of columns and their data types must match in all SELECT statements.

2. Use UNION ALL to include duplicates.

Examples of UNION Usage

Example 1: Simple UNION

Code:

-- Select distinct employee names from two departments
SELECT employee_name 
FROM department_a
UNION
SELECT employee_name 
FROM department_b;

Explanation:

• Combines employee names from both departments.
• Removes duplicates.

Example 2: Using UNION ALL

Code:

-- Select all employee names, including duplicates
SELECT employee_name 
FROM department_a
UNION ALL
SELECT employee_name 
FROM department_b;

Explanation:

• Includes all rows, even if they are duplicated.

Example 3: UNION with Additional Conditions

Code:

-- Combine distinct salaries from two tables with conditions
SELECT salary 
FROM employees
WHERE salary > 50000
UNION
SELECT salary 
FROM contractors
WHERE salary > 50000;

Explanation:

• Combines distinct salaries greater than 50,000 from both tables.

Example 4: Ordering Results in UNION

Code:

-- Combine results and sort the output
SELECT column1 
FROM table1
UNION
SELECT column1 
FROM table2
ORDER BY column1 ASC;

Explanation of code Examples:

• The ORDER BY clause applies to the entire combined result.

Explanation of Code Examples

• Default Deduplication: The UNION operator removes duplicate rows, ensuring the output is unique.
• Include Duplicates: Use UNION ALL for scenarios where retaining all rows is required.
• Conditions and Sorting: You can filter rows before combining and sort the final dataset.

Best Practices for Using UNION

1. Optimize Column Order: Ensure columns in SELECT statements match in number and data type.

2. Use UNION ALL When Possible: It avoids the overhead of removing duplicates, improving performance.

3. Test Query Output: Validate the results to ensure the combined data meets expectations.

Additional Notes:

• Alternative to UNION: If combining data without duplicates isn't needed, UNION ALL is faster.
• Performance Tip: Analyze query execution plans using EXPLAIN to measure the impact of UNION.
• Null Handling: Ensure consistent handling of NULL values in the combined datasets.

All PostgreSQL Questions, Answers, and Code Snippets Collection.