RIGHT JOIN in PostgreSQL 🚀

A RIGHT JOIN (also known as RIGHT OUTER JOIN) in PostgreSQL combines rows from two tables based on a matching condition but keeps all rows from the right table and only the matching rows from the left table. If no match is found, the result includes NULL for columns from the left table.

1. Basic Syntax

SELECT column1, column2FROM table1RIGHT JOIN table2ON table1.common_column = table2.common_column;

• The right table (table2) will include all of its rows, while the left table (table1) will only include matching rows.

2. Example: Get All Orders and Their Customers

SELECT orders.order_id, customers.customer_nameFROM ordersRIGHT JOIN customersON orders.customer_id = customers.id;

🔹 Returns all customers, including those without any orders, with NULL for order details where no match is found.

3. Example: Get Employees and Their Departments

SELECT employees.name, departments.department_nameFROM employeesRIGHT JOIN departmentsON employees.dept_id = departments.id;

🔹 Returns all departments, even if no employees are assigned to a department, with NULL for employee names where there’s no match.

4. Example: Get All Products and Categories (Even if Some Products Have No Category)

SELECT products.product_name, categories.category_nameFROM productsRIGHT JOIN categoriesON products.category_id = categories.id;

🔹 Returns all categories, and products will be shown where a match exists, with NULL for products that don’t belong to a category.

5. Example: RIGHT JOIN with WHERE Clause

SELECT orders.order_id, customers.nameFROM ordersRIGHT JOIN customersON orders.customer_id = customers.idWHERE orders.order_id IS NULL;

🔹 Returns customers with no orders (because NULL values are shown for missing order_id).

6. Example: RIGHT JOIN with Aggregation

SELECT departments.department_name, COUNT(employees.id) AS num_employeesFROM employeesRIGHT JOIN departmentsON employees.dept_id = departments.idGROUP BY departments.department_name;

🔹 Returns the total number of employees in each department, including departments with no employees (where the count will be 0).

7. Example: RIGHT JOIN with COALESCE to Handle NULL

SELECT employees.name, COALESCE(departments.department_name, 'No Department') AS departmentFROM employeesRIGHT JOIN departmentsON employees.dept_id = departments.id;

🔹 Uses COALESCE to replace NULL with 'No Department' for departments with no employees.

8. Example: RIGHT JOIN with ORDER BY

SELECT orders.order_id, customers.customer_nameFROM ordersRIGHT JOIN customersON orders.customer_id = customers.idORDER BY customers.customer_name;

🔹 Orders the results by customer name.

9. Difference Between RIGHT JOIN, LEFT JOIN, and INNER JOIN

Example Comparison:

Using RIGHT JOIN

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

🔹 All departments are shown, even if there are no employees in some departments.

Using LEFT JOIN

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

🔹 All employees are shown, even if they don’t belong to a department.

Using INNER JOIN

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

🔹 Only matching employees and departments are shown.

10. Performance Considerations

• Be mindful of large datasets, as RIGHT JOIN can return many rows if the right table is large.
• Ensure indexes are on the columns used in the ON clause to improve performance.
• Use EXPLAIN ANALYZE to analyze query performance.

Next Steps

✅ Use RIGHT JOIN to combine tables with different row counts
✅ Handle NULL values with COALESCE
✅ Integrate RIGHT JOIN in Go Fiber queries