LEFT JOIN in MySQL Workbench

• Let's explore LEFT JOINs in MySQL using an example.

• Continuing with the same hypothetical tables: `orders` and `customers`, I'll show you how a LEFT JOIN works.

Here's a reminder of what the tables look like:

• customers table:

    -- Create the 'customers' table

    CREATE TABLE customers (

        customer_id INT PRIMARY KEY,

        customer_name VARCHAR(50)

    );

    -- Insert data into the 'customers' table

    INSERT INTO customers (customer_id, customer_name)

    VALUES

        (101, 'Alice'),

        (102, 'Bob'),

        (103, 'Carol'),

        (104, 'David');

• orders table:

    -- Create the 'orders' table

    CREATE TABLE orders (

        order_id INT PRIMARY KEY,

        customer_id INT,

        order_date DATE,

        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

    );

    -- Insert data into the 'orders' table

    INSERT INTO orders (order_id, customer_id, order_date)

    VALUES

        (1, 101, '2023-08-01'),

        (2, 102, '2023-08-02'),

        (3, 103, '2023-08-02'),

        (4, 101, '2023-08-03');

• Now, let's say you want to retrieve a list of all orders along with the corresponding customer names. However, you also want to include orders that don't have a corresponding customer (i.e., orders made by unknown customers). This is where the LEFT JOIN comes into play:

    SELECT orders.order_id, orders.order_date, customers.customer_name

    FROM orders

    LEFT JOIN customers ON orders.customer_id = customers.customer_id;

In this query:

• orders and customers are the two tables you want to join.

• orders.customer_id is the column from the orders table that is related to the customers.customer_id column.

• The ON clause specifies the condition for the join. Here, it's matching rows where the customer_id values are equal in both tables.

The result of this query would be:

• Notice that all orders are included in the result, even if there isn't a matching customer. For example, the order with `order_id` 4 is still listed with its order details, even though there is no corresponding entry in the `customers` table.

• The LEFT JOIN retrieves all rows from the left table (`orders`) and only matching rows from the right table (`customers`). If there's no match in the right table, the columns from the right table will contain NULL values in the result. This is how you can include information from both tables while accounting for missing relationships.