RIGHT JOIN in MySQL Workbench

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

• We'll continue to work with the same hypothetical tables: `orders` and `customers`, and I'll show you how a RIGHT 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 customers along with the orders they have made. However, you also want to include customers who haven't made any orders (i.e., customers who are not in the orders table). This is where the RIGHT JOIN comes into play:

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

    FROM orders

    RIGHT 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 customers are included in the result, even if there isn't a matching order. For example, the customer with `customer_name` "David" is included in the list, even though there are no corresponding orders.

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