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

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

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

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

Hint

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

Practice

1. What happens when you use an INNER JOIN on columns that contain NULL values in SQL?

easy

A. Rows with NULL in join columns are always included.

B. Rows with NULL in join columns are excluded from the result.

C. The query returns an error due to NULL values.

D. Rows with NULL are treated as equal and joined together.

Solution

1. Step 1: Understand how INNER JOIN works with = condition

   When joining tables, SQL compares join columns using =. If a column has NULL, it means unknown, so comparison fails.

2. Step 2: Effect of NULL in join columns

   Since NULL = NULL is not true, rows with NULL in join columns do not match and are excluded.

3. Final Answer:

   Rows with NULL in join columns are excluded from the result. -> Option B

4. Quick Check:

   NULL means unknown, so no match [OK]

Hint: Remember: NULL never equals NULL in joins [OK]

Common Mistakes:

• Assuming NULL equals NULL in join conditions
• Expecting NULL rows to appear in INNER JOIN results
• Thinking NULL causes query errors in JOIN

2. Which of the following SQL JOIN syntaxes correctly includes rows with NULL values in the join column from the left table?

easy

A. SELECT * FROM A INNER JOIN B ON A.id = B.id;

B. SELECT * FROM A RIGHT JOIN B ON A.id = B.id;

C. SELECT * FROM A LEFT JOIN B ON A.id = B.id;

D. SELECT * FROM A FULL JOIN B ON A.id = B.id WHERE A.id IS NOT NULL;

Solution

1. Step 1: Identify JOIN types that keep left table rows

   LEFT JOIN keeps all rows from the left table, including those with NULL in join columns.

2. Step 2: Check other options

   INNER JOIN excludes unmatched rows; RIGHT JOIN keeps right table rows; FULL JOIN keeps all but filtering with WHERE A.id IS NOT NULL removes left table NULLs.

3. Final Answer:

   SELECT * FROM A LEFT JOIN B ON A.id = B.id; -> Option C

4. Quick Check:

   LEFT JOIN keeps left rows even if join column is NULL [OK]

Hint: Use LEFT JOIN to keep left table NULL rows [OK]

Common Mistakes:

• Using INNER JOIN expecting NULL rows to appear
• Filtering NULLs after FULL JOIN with WHERE clause
• Confusing RIGHT JOIN with LEFT JOIN behavior

3. Given tables:
Table A: id
1
2
NULL

Table B: id
2
NULL

What is the result of this query?

SELECT A.id, B.id FROM A INNER JOIN B ON A.id = B.id;

medium

A. (1, 2), (2, 2), (NULL, NULL)

B. (NULL, NULL)

C. (1, 2), (2, 2)

D. (2, 2)

Solution

1. Step 1: Understand INNER JOIN with NULLs

   INNER JOIN matches rows where A.id = B.id. NULLs do not match anything, even NULL.

2. Step 2: Check matching rows

   Only A.id = 2 matches B.id = 2. Rows with NULL do not match.

3. Final Answer:

   (2, 2) -> Option D

4. Quick Check:

   NULLs do not match, only 2 joins [OK]

Hint: NULLs never match in INNER JOIN conditions [OK]

Common Mistakes:

• Including NULL = NULL as a match
• Assuming all rows join regardless of NULL
• Confusing INNER JOIN with OUTER JOIN behavior

4. You wrote this query:

SELECT * FROM A LEFT JOIN B ON A.id = B.id WHERE B.id IS NOT NULL;

But it returns no rows, even though you expect rows from A with NULL id. What is the likely problem?

medium

A. The WHERE B.id IS NOT NULL filters out all rows including those with NULL in A.id.

B. LEFT JOIN does not include rows with NULL in join columns.

C. You should use INNER JOIN instead of LEFT JOIN.

D. The join condition A.id = B.id causes a syntax error.

Solution

1. Step 1: Understand LEFT JOIN with WHERE filter

   LEFT JOIN keeps all A rows, but WHERE B.id IS NOT NULL filters only rows where B.id is NOT NULL after join.

2. Step 2: Effect on rows with NULL in A.id

   Rows with NULL in A.id do not match B.id, so B.id is NULL after the join. The WHERE B.id IS NOT NULL condition excludes these rows.

3. Final Answer:

   The WHERE B.id IS NOT NULL filters out all rows including those with NULL in A.id. -> Option A

4. Quick Check:

   WHERE filters after join, can exclude expected rows [OK]

Hint: Remember WHERE filters after JOIN, can exclude NULL rows [OK]

Common Mistakes:

• Thinking LEFT JOIN excludes NULL join columns
• Using INNER JOIN expecting NULL rows
• Assuming join condition causes syntax error

5. You want to join tables A and B on id columns, but include rows where id is NULL in either table. Which query correctly returns all rows from both tables, matching on id including NULLs treated as equal?

hard

A. SELECT * FROM A FULL JOIN B ON A.id = B.id OR (A.id IS NULL AND B.id IS NULL);

B. SELECT * FROM A FULL JOIN B ON A.id = B.id;

C. SELECT * FROM A INNER JOIN B ON A.id = B.id OR (A.id IS NULL AND B.id IS NULL);

D. SELECT * FROM A LEFT JOIN B ON A.id = B.id WHERE A.id IS NOT NULL;

Solution

1. Step 1: Understand FULL JOIN and NULL behavior

   FULL JOIN returns all rows from both tables, but A.id = B.id excludes NULL matches because NULL = NULL is false.

2. Step 2: Add explicit NULL check in join condition

   Using OR (A.id IS NULL AND B.id IS NULL) treats NULLs as equal, including those rows in join results.

3. Step 3: Check other options

   SELECT * FROM A FULL JOIN B ON A.id = B.id;

   excludes NULL matches;

   SELECT * FROM A INNER JOIN B ON A.id = B.id OR (A.id IS NULL AND B.id IS NULL);

   uses INNER JOIN, excluding unmatched rows;

   SELECT * FROM A LEFT JOIN B ON A.id = B.id WHERE A.id IS NOT NULL;

   filters out NULLs.

4. Final Answer:

   SELECT * FROM A FULL JOIN B ON A.id = B.id OR (A.id IS NULL AND B.id IS NULL); -> Option A

5. Quick Check:

   Explicit NULL checks in JOIN condition include NULL matches [OK]

Hint: Add IS NULL checks in JOIN to match NULLs explicitly [OK]

Common Mistakes:

• Assuming NULL = NULL matches automatically
• Using INNER JOIN instead of FULL JOIN for all rows
• Filtering NULLs unintentionally with WHERE clause