PostgreSQL: Performing SQL LEFT JOINs

Combine data from multiple tables using PostgreSQL's LEFT JOIN. This guide demonstrates creating tables, inserting data, and performing left joins.

Performing SQL Joins in PostgreSQL

Creating and Populating a Table

This section demonstrates creating and populating a table in PostgreSQL. The `CREATE TABLE` statement defines the table structure, specifying column names, data types, and constraints (like primary keys). The `INSERT INTO` statement adds rows of data to the table.

CREATE TABLE Client_details (
    Phone_id INT PRIMARY KEY,
    Mobile_number BIGINT,
    address VARCHAR NOT NULL
);

INSERT INTO Client_details (Phone_id, Mobile_number, address)
VALUES
(1, 9976542310, 'Florida'),
(2, 9869456700, 'New York'),
(3, 7345672210, 'Chicago'),
(4, 9088506466, 'Houston'),
(5, 9476548901, 'Los Angeles');

Performing a LEFT JOIN

This section demonstrates using a `LEFT JOIN` to combine data from multiple tables (`Client`, `Orders`, and `Client_details`). A `LEFT JOIN` returns all rows from the left table (`Client` in this case), even if there's no match in the right tables. If there's a match, the corresponding columns from the right table are included; otherwise, those columns will have `NULL` values.

SELECT
    Client.client_name,
    order_id,
    order_date,
    Mobile_number
FROM
    Client
LEFT JOIN
    Client_details ON Client.Client_id = Client_details.Phone_id
LEFT JOIN
    Orders ON Client.client_id = Orders.client_id
ORDER BY
    Client.client_salary;

Retrieving Unmatched Records with LEFT JOIN

A `LEFT JOIN` is very useful for getting records from the left table that don't have a match in the right table. You can achieve this using a `WHERE` clause to filter for `NULL` values in the columns from the right table(s).

SELECT
    client_id,
    client_name,
    mobile_number
FROM
    Client
LEFT JOIN
    Client_details ON client_id = phone_id
WHERE
    mobile_number IS NULL;

This query will return clients who don't have an entry in the `Client_details` table.