Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL LEFT JOIN

The `LEFT JOIN` in PostgreSQL is a type of join that returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

Usage

The `LEFT JOIN` is used when you want to retrieve all records from the left table and the matched records from the right table. It is particularly useful for finding unmatched records in the right table.

sql
SELECT columns
FROM left_table
LEFT JOIN right_table ON left_table.common_column = right_table.common_column;

In this syntax, `LEFT JOIN` ensures that all rows from `left_table` are included, and matching rows from `right_table` are added where available.

Examples

1. Basic LEFT JOIN

sql
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

This example retrieves all employee names and their department names. If an employee is not assigned to a department, the department name will be NULL.

2. LEFT JOIN With a WHERE Clause

sql
SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.id
WHERE orders.order_date = '2023-10-01';

Here, all orders placed on October 1, 2023, are selected, including those without matching customer records, which will show `NULL` for `customer_name`.

3. Chaining Multiple LEFT JOINs

sql
SELECT products.product_name, categories.category_name, suppliers.supplier_name
FROM products
LEFT JOIN categories ON products.category_id = categories.id
LEFT JOIN suppliers ON products.supplier_id = suppliers.id;

This example shows how to use multiple `LEFT JOIN` clauses to bring together data from three tables: `products`, `categories`, and `suppliers`.

4. Handling NULL Values with COALESCE

sql
SELECT employees.name, COALESCE(departments.department_name, 'No Department') AS department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

In this example, `COALESCE` is used to replace `NULL` with 'No Department', providing a default value for unmatched records.

Tips and Best Practices

  • Use for ensuring inclusion. Apply `LEFT JOIN` when you need all records from the left table, regardless of matches in the right table.
  • Optimize with indexes. Ensure that the columns used in the `ON` clause are indexed for better performance. Be aware of potential performance impacts when working with large datasets and multiple joins.
  • Handle NULLs carefully. Be prepared to manage `NULL` values in your result set, especially when conducting further calculations or transformations. Use functions like `COALESCE` to handle `NULL` values effectively.
  • Combine with other clauses. Use `LEFT JOIN` in conjunction with `WHERE`, `GROUP BY`, or `ORDER BY` to refine and organize your results effectively.