PostgreSQL Order By Union

In Postgresql, UNION is an operator that merges the result sets of more than two SELECT statements into one result set. This operator places rows from the result set of the first query after, before, or among the rows from the result set of the second query. In this article, I’ll walk you through everything you need to know about using PostgreSQL Order By Union.

Table of Contents

When working with large datasets across multiple tables, you must combine results while maintaining specific sorting requirements. The UNION operator can do this. Understanding the methods below will improve your query performance.

Method 1: Basic Sorting with ORDER BY

The most straightforward approach is to apply an ORDER BY clause to the entire UNION result. The query below can be used for this purpose.

(SELECT product_name, price, 'Electronics' AS category FROM electronics)
UNION
(SELECT product_name, price, 'Appliances' AS category FROM appliances)
ORDER BY price DESC, product_name ASC;

After executing the above query, I got the expected output, as shown in the screenshot below.

Method 2: Using Subqueries

A subquery approach can handle more complex sorting needs. Let’s use the query below for this purpose.

SELECT * FROM (
    (SELECT product_id, product_name, price FROM electronics)
    UNION
    (SELECT product_id, product_name, price FROM appliances)
) AS combined_products
ORDER BY price DESC, product_name;

After executing the above query, I got the expected output, as shown in the screenshot below.

Method 3: Using CASE Statements

We can use the CASE Statement with ORDER BY using the below query.

SELECT product_name, price, inventory_count FROM (SELECT product_name, price, inventory_count FROM electronics
UNION
SELECT product_name, price, inventory_count FROM appliances)
ORDER BY 
    CASE 
        WHEN inventory_count = 0 THEN 2
        WHEN inventory_count < 10 THEN 1
        ELSE 0
    END,
    price DESC;

After executing the above query, I got the expected output, as shown in the screenshot below.

Method 4: Using Common Table Expressions

Using the query below, we can also use the ordering with the Common Table Expressions.

WITH combined_data AS (
    SELECT product_id, product_name, price, 'Electronics' AS category 
    FROM electronics
    UNION
    SELECT product_id, product_name, price, 'Appliances' AS category 
    FROM appliances
)
SELECT * FROM combined_data
ORDER BY category, price DESC;

After executing the above query, I got the expected output, as shown in the screenshot below.

Conclusion

Knowing PostgreSQL’s ORDER BY with UNION operation can extend the data retrieval capabilities. The key is understanding how to structure your queries properly and choosing the right approach based on your specific requirements, as mentioned in this article.