Three-valued logic (TRUE, FALSE, UNKNOWN) in SQL - Mini Project: Build & Apply

Understanding Three-Valued Logic in SQL

📖 Scenario: You are working with a database that stores information about employees and their project assignments. Some employees may not have been assigned to any project yet, so their project assignment is unknown (NULL).We want to understand how SQL handles conditions when some values are NULL, using three-valued logic: TRUE, FALSE, and UNKNOWN.

🎯 Goal: Build a simple SQL table with employee data including NULL values, then write queries that demonstrate how SQL evaluates conditions with TRUE, FALSE, and UNKNOWN results.

📋 What You'll Learn

Create a table called employees with columns id (integer), name (text), and project (text, can be NULL).

Insert exactly these rows into employees: (1, 'Alice', 'ProjectX'), (2, 'Bob', NULL), (3, 'Charlie', 'ProjectY').

Create a variable or temporary table to hold a condition that checks if project equals 'ProjectX'.

Write a SELECT query that uses the condition to filter employees and shows how three-valued logic works with NULLs.

💡 Why This Matters

🌍 Real World

Handling NULL values and understanding three-valued logic is essential when working with real databases where data may be incomplete or missing.

💼 Career

Database developers and analysts must know how SQL treats NULLs to write correct queries and avoid unexpected results in reports and applications.

1

Create the employees table and insert data

Write SQL statements to create a table called employees with columns id (integer), name (text), and project (text, nullable). Then insert these exact rows: (1, 'Alice', 'ProjectX'), (2, 'Bob', NULL), and (3, 'Charlie', 'ProjectY').

SQL

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

Hint

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

2

Define a condition to check for project 'ProjectX'

Write a SQL statement that defines a condition checking if the project column equals 'ProjectX'. Use this condition in a WHERE clause or as a named expression. For example, create a view or a common table expression (CTE) named project_check that selects all columns and adds a column is_project_x which is TRUE if project = 'ProjectX', FALSE otherwise, and UNKNOWN if project is NULL.

SQL

-- Define a condition to check if project equals 'ProjectX'
-- Your code here

Hint

Use a WITH clause to create a CTE that adds a boolean column showing the result of project = 'ProjectX'.

3

Write a query to select employees where project is 'ProjectX'

Write a SQL SELECT query that uses the project_check CTE and filters rows where is_project_x is TRUE. This will show which employees have project 'ProjectX' according to three-valued logic.

SQL

-- Select employees where is_project_x is TRUE
-- Your code here

Hint

Use WHERE is_project_x = TRUE to filter only rows where the condition is true.

4

Complete the query to show all employees with their condition results

Write a SQL query that selects all employees and shows the value of project = 'ProjectX' for each row, demonstrating TRUE, FALSE, and UNKNOWN results. Use the project_check CTE and select id, name, project, and is_project_x columns.

SQL

-- Select all employees with their is_project_x values
-- Your code here

Hint

Select all columns including the condition column to see TRUE, FALSE, and UNKNOWN values.

Practice

1. In SQL's three-valued logic, what is the result of comparing a column value to NULL using the = operator?

easy

A. TRUE

B. NULL

C. FALSE

D. UNKNOWN

Solution

1. Step 1: Understand comparison with NULL

   In SQL, any comparison with NULL using = does not return TRUE or FALSE but UNKNOWN.

2. Step 2: Recall three-valued logic rules

   Since NULL means unknown, the comparison result is UNKNOWN, not TRUE or FALSE.

3. Final Answer:

   UNKNOWN -> Option D

4. Quick Check:

   Comparison with NULL = UNKNOWN [OK]

Hint: Comparing with NULL using '=' always yields UNKNOWN [OK]

Common Mistakes:

• Assuming NULL equals NULL returns TRUE
• Thinking NULL comparison returns FALSE
• Confusing NULL with a string 'NULL'

2. Which of the following SQL WHERE clause conditions correctly checks if a column col contains NULL values?

easy

A. WHERE col = NULL

B. WHERE col != NULL

C. WHERE col IS NULL

D. WHERE col = 'NULL'

Solution

1. Step 1: Identify correct NULL check syntax

   In SQL, to check for NULL values, you must use IS NULL because = NULL does not work.

2. Step 2: Evaluate each option

   WHERE col IS NULL uses IS NULL, which is the correct syntax. Others compare with NULL using = or treat NULL as a string, which is incorrect.

3. Final Answer:

   WHERE col IS NULL -> Option C

4. Quick Check:

   Use IS NULL to check NULL values [OK]

Hint: Use IS NULL, not = NULL, to check for NULL values [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Comparing NULL as a string 'NULL'
• Using != NULL to find non-NULL values

3. Given a table Employees with a column Bonus that can be NULL, what will the query SELECT * FROM Employees WHERE Bonus > 1000; return for rows where Bonus is NULL?

medium

A. Rows with NULL Bonus are excluded

B. Rows with NULL Bonus are included

C. Query returns an error

D. Rows with NULL Bonus are returned as FALSE

Solution

1. Step 1: Understand comparison with NULL in WHERE clause

   Comparing NULL with any value using > results in UNKNOWN, which is treated as FALSE in WHERE filtering.

2. Step 2: Determine which rows are returned

   Rows where Bonus is NULL do not satisfy Bonus > 1000 condition, so they are excluded from the result.

3. Final Answer:

   Rows with NULL Bonus are excluded -> Option A

4. Quick Check:

   NULL comparison in WHERE excludes rows [OK]

Hint: WHERE excludes rows with NULL in condition comparisons [OK]

Common Mistakes:

• Assuming NULL > 1000 returns TRUE
• Expecting NULL rows to appear in results
• Thinking query throws error due to NULL

4. You wrote this query: SELECT * FROM Orders WHERE Status = 'Shipped' OR Status <> 'Shipped'; but it returns fewer rows than expected. What is the likely cause?

medium

A. Rows with NULL Status are excluded due to three-valued logic

B. Syntax error in the query

C. The OR operator is invalid here

D. The query returns all rows regardless of Status

Solution

1. Step 1: Analyze the WHERE clause logic

   The condition checks if Status equals 'Shipped' OR Status not equals 'Shipped'.

2. Step 2: Consider NULL values in Status

   For rows where Status is NULL, both comparisons return UNKNOWN, so those rows are excluded.

3. Final Answer:

   Rows with NULL Status are excluded due to three-valued logic -> Option A

4. Quick Check:

   NULL comparisons exclude rows in OR conditions [OK]

Hint: NULL in OR conditions can exclude rows unexpectedly [OK]

Common Mistakes:

• Assuming NULL rows satisfy OR condition
• Thinking query has syntax error
• Believing OR always returns all rows

5. You want to select all rows from Products where Discount is either greater than 20 or unknown (NULL). Which WHERE clause correctly implements this using three-valued logic?

hard

A. WHERE Discount > 20 OR Discount = NULL

B. WHERE Discount > 20 OR Discount IS NULL

C. WHERE Discount > 20 AND Discount IS NULL

D. WHERE Discount >= 20 OR Discount IS NOT NULL

Solution

1. Step 1: Identify correct NULL check syntax

   To check for NULL values, use IS NULL, not = NULL.

2. Step 2: Combine conditions correctly

   You want rows where Discount is greater than 20 OR Discount is NULL, so use OR with Discount > 20 and Discount IS NULL.

3. Final Answer:

   WHERE Discount > 20 OR Discount IS NULL -> Option B

4. Quick Check:

   Use IS NULL with OR for NULL checks [OK]

Hint: Use IS NULL with OR to include NULLs in conditions [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Using AND instead of OR
• Checking IS NOT NULL instead of IS NULL