PostgreSQL UNION

The `UNION` operator in PostgreSQL is used to combine the result sets of two or more `SELECT` queries into a single result set, eliminating duplicate rows. It ensures that the combined result set is unique. Ordering of the result set is not inherently done by `UNION` and must be explicitly specified with an `ORDER BY` clause.

Usage

The `UNION` operator is used when you need to aggregate results from multiple `SELECT` statements into one cohesive dataset. It is particularly useful for merging tables with similar structures.

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
Được hỗ trợ bởi

In this syntax, `UNION` merges the outputs of the two `SELECT` statements, discarding any duplicate rows. The implicit column names in the result set are derived from the first `SELECT` statement.

Examples

1. Basic UNION

SELECT city
FROM customers
UNION
SELECT city
FROM suppliers;
Được hỗ trợ bởi

This example merges a list of cities from the `customers` and `suppliers` tables, removing duplicates to give a unique list.

2. UNION with Different Column Names

SELECT first_name AS name
FROM employees
UNION
SELECT supplier_name AS name
FROM suppliers;
Được hỗ trợ bởi

Here, columns with different names are combined using `UNION`. The alias `name` applies to the result set, and consistency in aliases is crucial for readability and maintenance. The columns must be of the same data type.

3. UNION with ORDER BY

SELECT product_name
FROM products
UNION
SELECT service_name
FROM services
ORDER BY 1;
Được hỗ trợ bởi

This example combines product and service names into one list and orders the entire result set alphabetically. The `ORDER BY` clause uses the column position, which is generally recommended to avoid ambiguity.

Tips and Best Practices

• Ensure column compatibility. The number and data types of columns in each `SELECT` must match.
• Use `UNION ALL` for performance. If duplicates are acceptable, `UNION ALL` avoids the overhead of sorting and duplicate removal.
• Be cautious with `ORDER BY`. Only the final `SELECT` can include an `ORDER BY` clause, affecting the entire result set. Reference output column names or use column positions to avoid ambiguity.
• Check for NULL handling. Ensure columns across queries handle `NULL` values consistently to prevent unexpected results.
• Consider indexing. Indexing tables used in `UNION` queries can optimize performance by speeding up the retrieval of data.
• Understand performance implications. Using `UNION` may involve additional processing for sorting and duplicate elimination compared to `UNION ALL`.