PostgreSQL - LEFT JOIN

The LEFT JOIN in PostgreSQL returns all rows from the left table and the matching rows from the right table. If no matching row exists in the right table, the result contains NULL values for the right table columns. The LEFT JOIN helps in:

• Returning all records from the left table.
• Retrieving matching records from the right table.
• Displaying NULL values when no matching record exists.
• Finding records with or without related data.

Syntax

SELECT table1.column1, table1.column2, table2.column1, ...
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Example

Firstly, create the Customers and Orders tables and insert some records.

Customers Table

Orders Table

The following query returns all customers along with their orders. Customers without any orders are also included in the result.

Query:

SELECT
    c.CustomerName,
    o.ProductName
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID;

Output:

Example: LEFT JOIN with WHERE Clause

The following query retrieves customers who have not placed any orders.

Query:

SELECT
    c.CustomerName,
    o.ProductName
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

Output:

• The LEFT JOIN returns all records from the Customers table.
• Matching rows from the Orders table are included.
• The WHERE o.OrderID IS NULL condition filters customers who do not have any matching orders.