UNION in PostgreSQL 🚀

The UNION operator in PostgreSQL is used to combine results from multiple SELECT queries into a single result set. It removes duplicate rows by default.

1. Basic Syntax

SELECT column1, column2 FROM table1UNIONSELECT column1, column2 FROM table2;

• The number of columns and their data types must match in both queries.
• UNION automatically removes duplicates.

2. Example: Combine Users from Two Tables

SELECT name, email FROM customersUNIONSELECT name, email FROM employees;

🔹 Returns a list of unique names and emails from both tables.

3. Example: Union of Two SELECT Queries

SELECT id, product_name FROM products WHERE category = 'Electronics'UNIONSELECT id, product_name FROM products WHERE category = 'Appliances';

🔹 Returns unique products from both categories.

4. Example: Using UNION ALL (Keeps Duplicates)

SELECT name FROM usersUNION ALLSELECT name FROM admins;

🔹 UNION ALL does not remove duplicates, making it faster.

5. Example: Union with Different Conditions

SELECT id, name, 'Customer' AS role FROM customersUNIONSELECT id, name, 'Supplier' AS role FROM suppliers;

🔹 Adds a role column to differentiate between customers and suppliers.

6. Example: Union with ORDER BY

SELECT id, name FROM usersUNIONSELECT id, name FROM adminsORDER BY name ASC;

🔹 Sorting applies after the UNION operation.

7. Example: Union with LIMIT and OFFSET

SELECT id, name FROM customersUNIONSELECT id, name FROM employeesLIMIT 10 OFFSET 5;

🔹 Retrieves 10 rows after skipping the first 5 rows.

8. Example: Combining Data from Different Date Ranges

SELECT order_id, order_date FROM orders WHERE order_date >= '2024-01-01'UNIONSELECT order_id, order_date FROM orders_archive WHERE order_date < '2024-01-01';

🔹 Merges orders from active and archived tables.

9. Example: Using UNION in a Subquery

SELECT name FROM (    SELECT name FROM users    UNION    SELECT name FROM admins) AS combined_usersWHERE name LIKE 'A%';

🔹 Filters only names starting with "A" after applying UNION.

10. Difference Between UNION and JOIN

Example Comparison

Using UNION

SELECT id, name FROM customersUNIONSELECT id, name FROM employees;

🔹 Combines two tables with the same structure.

Using JOIN

SELECT customers.name, orders.amountFROM customersJOIN orders ON customers.id = orders.customer_id;

🔹 Merges related data from different tables.

11. Performance Tips

• Use UNION ALL instead of UNION if you don’t need duplicate removal (it's faster).
• Ensure column data types match in all queries.
• Use indexes on frequently queried columns to improve speed.

Next Steps

✅ Optimize UNION queries for performance
✅ Use UNION with Go Fiber queries
✅ Explore JOIN vs UNION for different use cases