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:
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:
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:
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:
Explanation of Code Examples
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:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4