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

Need a 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

Need a 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

Need a 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

Need a hint?

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