FULL JOIN in PostgreSQL 🚀

A FULL JOIN (also known as FULL OUTER JOIN) in PostgreSQL combines all rows from both tables. If a match exists, it merges the data; otherwise, it includes NULL for missing values.

1. Basic Syntax

SELECT *FROM table1FULL JOIN table2ON table1.common_column = table2.common_column;

• Includes all rows from both tables.
• If no match exists, NULL is returned for missing columns.

2. Example: Merging Customers and Orders

SELECT customers.id, customers.name, orders.id AS order_id, orders.amountFROM customersFULL JOIN ordersON customers.id = orders.customer_id;

🔹 Returns all customers and all orders.
🔹 Customers without orders and orders without customers appear with NULL.

3. Example: Joining Two Employee Lists

SELECT e1.id AS old_id, e1.name AS old_name,        e2.id AS new_id, e2.name AS new_nameFROM employees_old e1FULL JOIN employees_new e2ON e1.id = e2.id;

🔹 Shows both old and new employees in one list.
🔹 Employees only in one table appear with NULL.

4. Example: Using FULL JOIN with COALESCE

SELECT COALESCE(a.id, b.id) AS id, a.name AS old_name, b.name AS new_nameFROM employees_old aFULL JOIN employees_new bON a.id = b.id;

🔹 Uses COALESCE() to fill missing IDs when there's no match.

5. Example: Using FULL JOIN with WHERE to Find Unmatched Records

SELECT *FROM customers cFULL JOIN orders oON c.id = o.customer_idWHERE c.id IS NULL OR o.customer_id IS NULL;

🔹 Shows customers without orders and orders without customers.

6. Difference Between FULL JOIN, LEFT JOIN, and RIGHT JOIN

Example Comparison

Using FULL JOIN

SELECT * FROM employeesFULL JOIN departments ON employees.dept_id = departments.id;

🔹 Returns all employees and all departments, even if there's no match.

Using LEFT JOIN

SELECT * FROM employeesLEFT JOIN departments ON employees.dept_id = departments.id;

🔹 Returns all employees with department details (or NULL if no department).

Using RIGHT JOIN

SELECT * FROM employeesRIGHT JOIN departments ON employees.dept_id = departments.id;

🔹 Returns all departments with employee details (or NULL if no employees).

7. Example: Using FULL JOIN with Aggregation

SELECT COALESCE(customers.id, orders.customer_id) AS id,        customers.name,        SUM(orders.amount) AS total_spentFROM customersFULL JOIN orders ON customers.id = orders.customer_idGROUP BY customers.id, customers.name;

🔹 Combines customers and orders, even if some customers have no orders.

8. Performance Considerations

• Can be slow on large tables (returns a lot of data).
• Use WHERE to filter unnecessary results.
• Indexing common columns can improve performance.

Next Steps

✅ Optimize FULL JOIN queries
✅ Use FULL JOIN in Go Fiber queries
✅ Compare FULL JOIN with LEFT JOIN and RIGHT JOIN