PostgreSQL LEFT JOIN

PostgreSQL LEFT JOIN

A LEFT JOIN (or LEFT OUTER JOIN) is used to return all rows from the left table (the first table in the join), and the matched rows from the right table (the second table). If there is no match, the result will contain NULL columns from the right table.

1. Basic Syntax of LEFT JOIN

SELECT column1, column2, ...
FROM left_table
LEFT JOIN right_table
ON left_table.column = right_table.column;

• left_table: The table on the left side of the join.
• right_table: The table on the right side of the join.
• ON left_table.column = right_table.column: The condition to match rows between the two tables.

2. Example: Using LEFT JOIN to Fetch Data from Two Tables

Example 1: Get All Customers and Their Orders

Consider two tables:

• customers (with customer_id, first_name, and last_name).
• orders (with order_id, customer_id, and order_date).

Query: Retrieve All Customers and Their Orders

SELECT customers.first_name, customers.last_name, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

• This query returns all customers along with their order dates. If a customer has no orders, the order_date will be NULL.

Result:

first_name	last_name	order_date
John	Doe	2025-01-01
Jane	Smith	NULL
Alice	Johnson	2025-01-05

• Note: Jane Smith has no orders, so her order_date is NULL.

3. Example: Left Join with Multiple Tables

Example 2: Get Employees and Their Assigned Projects

Consider:

• employees table: (employee_id, first_name, last_name).
• projects table: (project_id, employee_id, project_name).

Query: Retrieve All Employees and Their Projects

SELECT employees.first_name, employees.last_name, projects.project_name
FROM employees
LEFT JOIN projects
ON employees.employee_id = projects.employee_id;

• This query returns all employees and their associated project names. If an employee is not assigned to any project, the project_name will be NULL.

4. Example: Left Join with Filtering Using WHERE Clause

Example 3: Get All Customers and Orders from a Specific Date

Query: Retrieve All Customers and Orders Made on '2025-01-01'

SELECT customers.first_name, customers.last_name, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
WHERE orders.order_date = '2025-01-01' OR orders.order_date IS NULL;

• This query returns customers and their orders for a specific date. Customers without orders will have NULL values in the order_date.

5. Performance Considerations

• Efficiency: LEFT JOIN can be slower than INNER JOIN because it needs to include rows with NULL values for non-matching rows from the right table.
• Indexes: If possible, create indexes on the columns used for the join condition to improve performance.

6. Summary of LEFT JOIN

Operation	SQL Command
Retrieve All Rows from Left Table	SELECT * FROM left_table LEFT JOIN right_table ON condition;
Get All Employees and Their Projects	SELECT employees.first_name, employees.last_name, projects.project_name FROM employees LEFT JOIN projects ON employees.employee_id = projects.employee_id;
Return Rows with NULL for Non-Matching Data	Non-matching rows from the right table will have NULL values.
When to Use	Use when you want to keep all rows from the left table, even if there is no matching data in the right table.

Would you like to see more examples or use cases for LEFT JOIN? 🚀