NULLs in JOIN conditions in SQL - Mini Project: Build & Apply

Handling NULLs in JOIN Conditions

📖 Scenario: You work at a company that manages customer orders. Some orders have assigned sales representatives, but some do not yet have one assigned, so the sales_rep_id can be NULL.You want to create a report that shows all orders along with the sales representative's name if assigned, or show 'No Rep Assigned' if not.

🎯 Goal: Create two tables orders and sales_reps. Then write a SQL query that joins these tables on sales_rep_id, correctly handling NULL values so that orders without a sales rep still appear in the results with a placeholder text.

📋 What You'll Learn

Create a table called orders with columns order_id (integer), customer_name (text), and sales_rep_id (integer, can be NULL).

Create a table called sales_reps with columns sales_rep_id (integer) and rep_name (text).

Insert the exact data provided into both tables.

Write a SQL query that LEFT JOINs orders with sales_reps on sales_rep_id, handling NULLs so that orders without a sales rep show 'No Rep Assigned' in the output.

Use COALESCE or CASE to replace NULL sales rep names with 'No Rep Assigned'.

💡 Why This Matters

🌍 Real World

Handling NULLs in JOIN conditions is common in real databases where relationships may be optional, such as orders without assigned sales reps.

💼 Career

Database developers and analysts often write queries that must handle NULLs correctly to produce accurate reports and avoid missing data.

1

Create the orders table and insert data

Create a table called orders with columns order_id (integer), customer_name (text), and sales_rep_id (integer, nullable). Then insert these exact rows: (1, 'Alice', 101), (2, 'Bob', NULL), (3, 'Charlie', 102).

SQL

-- Create the orders table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows. Remember that sales_rep_id can be NULL.

2

Create the sales_reps table and insert data

Create a table called sales_reps with columns sales_rep_id (integer) and rep_name (text). Then insert these exact rows: (101, 'John Doe'), (102, 'Jane Smith').

SQL

CREATE TABLE orders (
  order_id INTEGER,
  customer_name TEXT,
  sales_rep_id INTEGER
);

INSERT INTO orders (order_id, customer_name, sales_rep_id) VALUES
(1, 'Alice', 101),
(2, 'Bob', NULL),
(3, 'Charlie', 102);

-- Create the sales_reps table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE and INSERT INTO similarly as before for the sales_reps table.

3

Write a LEFT JOIN query to combine orders with sales reps

Write a SQL query that selects order_id, customer_name, and rep_name from orders LEFT JOIN sales_reps on orders.sales_rep_id = sales_reps.sales_rep_id. This will include all orders even if sales_rep_id is NULL.

SQL

CREATE TABLE orders (
  order_id INTEGER,
  customer_name TEXT,
  sales_rep_id INTEGER
);

INSERT INTO orders (order_id, customer_name, sales_rep_id) VALUES
(1, 'Alice', 101),
(2, 'Bob', NULL),
(3, 'Charlie', 102);

CREATE TABLE sales_reps (
  sales_rep_id INTEGER,
  rep_name TEXT
);

INSERT INTO sales_reps (sales_rep_id, rep_name) VALUES
(101, 'John Doe'),
(102, 'Jane Smith');

-- Write a LEFT JOIN query to get order_id, customer_name, and rep_name
-- Your code here

Need a hint?

Use LEFT JOIN to include all orders, even those without a matching sales rep.

4

Handle NULL sales rep names with COALESCE

Modify the previous query to replace NULL rep_name values with the text 'No Rep Assigned' using COALESCE. Select order_id, customer_name, and the modified rep_name as sales_rep.

SQL

CREATE TABLE orders (
  order_id INTEGER,
  customer_name TEXT,
  sales_rep_id INTEGER
);

INSERT INTO orders (order_id, customer_name, sales_rep_id) VALUES
(1, 'Alice', 101),
(2, 'Bob', NULL),
(3, 'Charlie', 102);

CREATE TABLE sales_reps (
  sales_rep_id INTEGER,
  rep_name TEXT
);

INSERT INTO sales_reps (sales_rep_id, rep_name) VALUES
(101, 'John Doe'),
(102, 'Jane Smith');

-- Modify the query to use COALESCE to replace NULL rep_name with 'No Rep Assigned'
-- Your code here

Need a hint?

Use COALESCE(column, 'default') to replace NULL values in the rep_name column.