LEFT JOIN execution behavior in SQL - Mini Project: Build & Apply

Understanding LEFT JOIN Execution Behavior in SQL

📖 Scenario: You are working with two tables in a small business database: Customers and Orders. You want to learn how to use a LEFT JOIN to see all customers and their orders, including customers who have not placed any orders yet.

🎯 Goal: Build a SQL query using LEFT JOIN to combine the Customers and Orders tables, showing all customers and their orders if any.

📋 What You'll Learn

Create a table called Customers with columns CustomerID and CustomerName and insert exact data.

Create a table called Orders with columns OrderID, CustomerID, and OrderDate and insert exact data.

Write a LEFT JOIN query to list all customers and their orders, including customers without orders.

Select CustomerName and OrderDate in the query.

💡 Why This Matters

🌍 Real World

LEFT JOIN is commonly used in business databases to combine customer information with their orders, even if some customers have not placed any orders yet.

💼 Career

Understanding LEFT JOIN helps in data analysis, reporting, and building applications that need to show complete lists with optional related data.

1

Create the Customers table and insert data

Create a table called Customers with columns CustomerID (integer) and CustomerName (text). Insert these exact rows: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie').

SQL

-- Create Customers table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as shown.

2

Create the Orders table and insert data

Create a table called Orders with columns OrderID (integer), CustomerID (integer), and OrderDate (text). Insert these exact rows: (101, 1, '2024-01-10'), (102, 1, '2024-02-15'), (103, 2, '2024-03-20').

SQL

CREATE TABLE Customers (
    CustomerID INTEGER,
    CustomerName TEXT
);

INSERT INTO Customers (CustomerID, CustomerName) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Create Orders table and insert data
-- Your code here

Need a hint?

Define the Orders table with the specified columns and insert the rows exactly as given.

3

Write a LEFT JOIN query to combine Customers and Orders

Write a SQL query that uses LEFT JOIN to join Customers and Orders on CustomerID. Select CustomerName and OrderDate from the joined tables.

SQL

CREATE TABLE Customers (
    CustomerID INTEGER,
    CustomerName TEXT
);

INSERT INTO Customers (CustomerID, CustomerName) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE Orders (
    OrderID INTEGER,
    CustomerID INTEGER,
    OrderDate TEXT
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(101, 1, '2024-01-10'),
(102, 1, '2024-02-15'),
(103, 2, '2024-03-20');

-- Write LEFT JOIN query selecting CustomerName and OrderDate
-- Your code here

Need a hint?

Use LEFT JOIN to include all customers, even those without orders.

4

Complete the query with ORDER BY clause

Add an ORDER BY clause to the query to sort the results by CustomerName in ascending order.

SQL

CREATE TABLE Customers (
    CustomerID INTEGER,
    CustomerName TEXT
);

INSERT INTO Customers (CustomerID, CustomerName) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE Orders (
    OrderID INTEGER,
    CustomerID INTEGER,
    OrderDate TEXT
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(101, 1, '2024-01-10'),
(102, 1, '2024-02-15'),
(103, 2, '2024-03-20');

SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
-- Add ORDER BY clause here

Need a hint?

Use ORDER BY Customers.CustomerName ASC to sort the results alphabetically by customer name.