NULL in AND, OR, NOT logic in SQL - Mini Project: Build & Apply

Understanding NULL in AND, OR, NOT Logic in SQL

📖 Scenario: You are working with a simple employee database. Some employees have missing information in their records, represented by NULL values. You want to understand how SQL handles NULL values when using logical operators AND, OR, and NOT.

🎯 Goal: Create a table with employee data including some NULL values, then write queries using AND, OR, and NOT to see how NULL affects the results.

📋 What You'll Learn

Create a table called employees with columns id, name, and department.

Insert exactly 5 rows with some NULL values in the department column.

Write a query using AND to select employees in the 'Sales' department and with a non-NULL department.

Write a query using OR to select employees who are in the 'Sales' department or have a NULL department.

Write a query using NOT to select employees who are not in the 'Sales' department.

💡 Why This Matters

🌍 Real World

Handling NULL values correctly is important in real databases because missing data is common. Understanding how NULL interacts with logical operators helps avoid bugs in queries.

💼 Career

Database developers and analysts must write queries that correctly handle NULLs to ensure accurate data filtering and reporting.

1

Create the employees table and insert data

Create a table called employees with columns id (integer), name (text), and department (text). Insert these exact rows: (1, 'Alice', 'Sales'), (2, 'Bob', NULL), (3, 'Charlie', 'HR'), (4, 'Diana', NULL), (5, 'Eve', 'Sales').

SQL

-- Create the employees table and insert the 5 rows below
-- Your code here

Hint

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

2

Add a query using AND to filter employees

Write a query that selects all columns from employees where department is 'Sales' and department is not NULL. Use WHERE department = 'Sales' AND department IS NOT NULL.

SQL

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', NULL),
(3, 'Charlie', 'HR'),
(4, 'Diana', NULL),
(5, 'Eve', 'Sales');

-- Write a SELECT query using AND to find employees in Sales with non-NULL department
-- Your code here

Hint

Use WHERE with AND and check for IS NOT NULL to exclude NULL values.

3

Add a query using OR to include NULL departments

Write a query that selects all columns from employees where department is 'Sales' or department is NULL. Use WHERE department = 'Sales' OR department IS NULL.

SQL

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', NULL),
(3, 'Charlie', 'HR'),
(4, 'Diana', NULL),
(5, 'Eve', 'Sales');

SELECT * FROM employees WHERE department = 'Sales' AND department IS NOT NULL;

-- Write a SELECT query using OR to find employees in Sales or with NULL department
-- Your code here

Hint

Use OR to include rows where department is NULL.

4

Add a query using NOT to exclude 'Sales' department

Write a query that selects all columns from employees where department is not 'Sales'. Use WHERE NOT department = 'Sales'.

SQL

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  department TEXT
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', NULL),
(3, 'Charlie', 'HR'),
(4, 'Diana', NULL),
(5, 'Eve', 'Sales');

SELECT * FROM employees WHERE department = 'Sales' AND department IS NOT NULL;

SELECT * FROM employees WHERE department = 'Sales' OR department IS NULL;

-- Write a SELECT query using NOT to find employees not in Sales
-- Your code here

Hint

Use NOT before the condition to exclude 'Sales' department employees.

Practice

1. What is the result of the SQL expression TRUE AND NULL?

easy

A. TRUE

B. NULL

C. FALSE

D. Error

Solution

1. Step 1: Understand AND with NULL

   In SQL, AND returns FALSE if any side is FALSE. If one side is TRUE and the other is NULL, the result is NULL because the unknown value affects the outcome.

2. Step 2: Apply to TRUE AND NULL

   Since the first operand is TRUE and the second is NULL, the result is NULL.

3. Final Answer:

   NULL -> Option B

4. Quick Check:

   TRUE AND NULL = NULL [OK]

Hint: AND with NULL returns NULL unless FALSE present [OK]

Common Mistakes:

• Assuming TRUE AND NULL is TRUE
• Confusing NULL with FALSE
• Thinking NULL causes error

2. Which of the following is the correct SQL syntax to check if a column col is NOT NULL?

easy

A. col != NULL

B. col <> NULL

C. col IS NOT NULL

D. NOT col = NULL

Solution

1. Step 1: Recall NULL comparison rules

   In SQL, you cannot use = or != to compare with NULL. Instead, use IS NULL or IS NOT NULL.

2. Step 2: Identify correct syntax

   The correct way to check if col is not null is col IS NOT NULL.

3. Final Answer:

   col IS NOT NULL -> Option C

4. Quick Check:

   Use IS NOT NULL for null checks [OK]

Hint: Use IS NOT NULL, not != or <> [OK]

Common Mistakes:

• Using != or <> to compare with NULL
• Using NOT col = NULL which is invalid
• Confusing NULL with empty string

3. Given a table Test with a column flag that can be TRUE, FALSE, or NULL, what is the result of this query?

SELECT * FROM Test WHERE flag OR FALSE;

medium

A. Rows where flag is TRUE

B. Rows where flag is TRUE or NULL

C. Rows where flag is TRUE or FALSE

D. No rows returned

Solution

1. Step 1: Understand OR with FALSE

   In SQL, OR FALSE returns the value of the first operand because FALSE does not affect the OR result.

2. Step 2: Evaluate flag OR FALSE

   If flag is TRUE, the expression is TRUE. If flag is FALSE, expression is FALSE. If flag is NULL, expression is NULL (unknown), which is treated as FALSE in WHERE filtering.

3. Final Answer:

   Rows where flag is TRUE -> Option A

4. Quick Check:

   flag OR FALSE = flag; WHERE filters TRUE only [OK]

Hint: OR FALSE returns first operand value [OK]

Common Mistakes:

• Including NULL rows in result
• Thinking OR FALSE always returns TRUE
• Confusing OR with AND behavior

4. Consider this SQL WHERE clause:

WHERE NOT (col = NULL)

What is the problem with this condition?

medium

A. It always returns NULL (no rows match)

B. It always returns TRUE

C. It always returns FALSE

D. It causes a syntax error

Solution

1. Step 1: Understand comparison with NULL

   In SQL, col = NULL always returns NULL (unknown), never TRUE or FALSE.

2. Step 2: Apply NOT to NULL

   NOT NULL is still NULL, so the condition never evaluates to TRUE, filtering out all rows.

3. Final Answer:

   It always returns NULL (no rows match) -> Option A

4. Quick Check:

   NOT (col = NULL) = NULL, no matches [OK]

Hint: NOT NULL remains NULL, no rows pass filter [OK]

Common Mistakes:

• Expecting NOT NULL to be TRUE
• Using = NULL instead of IS NULL
• Assuming syntax error occurs

5. You want to select rows from a table Employees where the ManagerID is either NULL or the employee is active (Active = TRUE). Which WHERE clause correctly handles NULL logic?

hard

A. WHERE ManagerID IS NOT NULL OR Active = TRUE

B. WHERE ManagerID = NULL OR Active = TRUE

C. WHERE NOT ManagerID = NULL AND Active = TRUE

D. WHERE ManagerID IS NULL OR Active = TRUE

Solution

1. Step 1: Correctly check for NULL

   Use IS NULL to check if ManagerID is NULL. Using = NULL is invalid and returns NULL.

2. Step 2: Combine conditions with OR

   The condition requires rows where ManagerID is NULL or Active is TRUE, so use OR between these checks.

3. Final Answer:

   WHERE ManagerID IS NULL OR Active = TRUE -> Option D

4. Quick Check:

   Use IS NULL and OR for correct logic [OK]

Hint: Use IS NULL, combine with OR for either condition [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Using AND instead of OR
• Using IS NOT NULL which is opposite