Write a query to get all customers and their orders using LEFT JOIN.

Objective:

Write a SQL query to get all customers and their orders using LEFT JOIN.

That means:

• Show all customers, even if they have no orders.
• Use LEFT JOIN between customers and orders tables.

Step 1: Create the Tables

We'll create two tables:

1. customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(50)
);

2. orders Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(10,2),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Step 2: Insert Dummy Data

Insert into customers:

INSERT INTO customers (customer_id, customer_name, city) VALUES
(1, 'Alice Smith', 'New York'),
(2, 'Bob Johnson', 'Los Angeles'),
(3, 'Charlie Lee', 'Chicago'),
(4, 'Diana Patel', 'Houston');

Insert into orders:

INSERT INTO orders (order_id, order_date, amount, customer_id) VALUES
(101, '2024-01-15', 250.00, 1),
(102, '2024-02-10', 500.00, 1),
(103, '2024-03-05', 750.00, 3),
(104, '2024-03-18', 320.00, 3);

Note: Bob (ID 2) and Diana (ID 4) do not have any orders.

Step 3: LEFT JOIN Query

SELECT 
    c.customer_id,
    c.customer_name,
    c.city,
    o.order_id,
    o.order_date,
    o.amount
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id;

Step 4: Output

Explanation:

• LEFT JOIN keeps all rows from customers.
• If a customer has no orders, the order_id, order_date, and amount are shown as NULL.
• This is ideal for reporting customers who haven’t ordered yet.