LEFT JOIN in PostgreSQL 🚀

A LEFT JOIN (also known as LEFT OUTER JOIN) in PostgreSQL combines rows from two tables, keeping all rows from the left table and matching rows from the right table. If there’s no match, the result includes NULL for columns from the right table.

1. Basic Syntax

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

• table1 (left table) will have all rows returned.
• table2 (right table) will include matching rows based on the ON condition. If no match, NULL values will be shown for columns from table2.

2. Example: Get All Employees and Their Departments

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

🔹 Returns all employees and their corresponding departments, even if some employees don’t belong to a department (those without a department will have NULL in department_name).

3. Example: Get All Orders and Their Customers

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

🔹 Returns all orders and their corresponding customers, even if some orders don’t have a customer.

4. Example: LEFT JOIN with WHERE Clause

SELECT employees.name, departments.department_nameFROM employeesLEFT JOIN departmentsON employees.dept_id = departments.idWHERE departments.department_name IS NULL;

🔹 Finds employees without a department, because NULL is returned when there's no match.

5. Example: LEFT JOIN with Aggregation

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

🔹 Returns all departments and counts the number of employees in each. Departments without employees will show 0 employees.

6. Example: LEFT JOIN with COALESCE to Handle NULL Values

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

🔹 Replaces NULL values in department_name with 'No Department' for employees without a department.

7. Example: LEFT JOIN with ORDER BY

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

🔹 Sorts the results by customer name.

8. Example: LEFT JOIN with Multiple Tables

SELECT orders.order_id, customers.name, products.product_nameFROM ordersLEFT JOIN customersON orders.customer_id = customers.idLEFT JOIN productsON orders.product_id = products.id;

🔹 Returns all orders, with corresponding customers and products, even if there’s no match for customer or product.

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

Example Comparison:

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 RIGHT JOIN

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

🔹 All departments are shown, even if no employees belong to some departments.

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 LEFT JOIN can return many rows if the left table is large.
• Ensure indexes are on columns used in the ON clause to improve performance.
• Use EXPLAIN ANALYZE to test query performance.

Next Steps

✅ Use LEFT JOIN for one-to-many relationships
✅ Handle NULL values with COALESCE
✅ Integrate LEFT JOIN in Go Fiber queries