Boolean column filtering patterns in PostgreSQL - Step-by-Step Execution

Concept Flow - Boolean column filtering patterns

Start with table

↓

Apply WHERE clause

↓

Check Boolean column value

↓

Include row

↓

Return filtered rows

The query filters rows by checking the Boolean column in the WHERE clause, including only rows where the condition is TRUE.

Execution Sample

PostgreSQL

SELECT * FROM tasks WHERE completed = TRUE;

This query returns all rows from the tasks table where the completed column is TRUE.

Execution Table

Step	Row ID	completed value	Condition (completed = TRUE)	Include in Result
1	1	TRUE	TRUE = TRUE	Yes
2	2	FALSE	FALSE = TRUE	No
3	3	TRUE	TRUE = TRUE	Yes
4	4	NULL	NULL = TRUE (unknown)	No
5	5	FALSE	FALSE = TRUE	No
6	6	TRUE	TRUE = TRUE	Yes
Exit	-	-	No more rows	Filtering complete

💡 All rows checked; only rows with completed = TRUE included.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6	Final
completed	N/A	TRUE	FALSE	TRUE	NULL	FALSE	TRUE	N/A
Condition Result	N/A	TRUE	FALSE	TRUE	UNKNOWN	FALSE	TRUE	N/A
Included in Result	N/A	Yes	No	Yes	No	No	Yes	N/A

Key Moments - 2 Insights

Why are rows with NULL in the Boolean column not included?

Because in SQL, NULL means unknown, and the condition 'completed = TRUE' evaluates to unknown, which is treated as false in WHERE filtering (see execution_table row 4).

What happens if we write 'WHERE completed' instead of 'WHERE completed = TRUE'?

Both are equivalent in PostgreSQL; the condition checks if completed is true. The execution flow and results remain the same.

Visual Quiz - 3 Questions

Look at the execution_table, at step 2, what is the condition result for completed = TRUE?

A TRUE

B FALSE

C UNKNOWN

D NULL

💡 Hint

Check the 'Condition (completed = TRUE)' column at step 2 in the execution_table.

At which step does the condition evaluate to UNKNOWN?

A Step 4

B Step 3

C Step 5

D Step 6

💡 Hint

Look for 'NULL = TRUE (unknown)' in the Condition column in the execution_table.

If we change the query to 'WHERE completed = FALSE', which row(s) would be included?

A Rows 4 and 5

B Rows 1, 3, and 6

C Rows 2 and 5

D Rows 1 and 6

💡 Hint

Refer to the 'completed value' column and think which rows have FALSE.

Concept Snapshot

Boolean column filtering in SQL:
Use WHERE column = TRUE to select rows where the Boolean column is true.
Rows with FALSE or NULL are excluded.
NULL means unknown and does not pass the filter.
'WHERE column' is shorthand for 'WHERE column = TRUE'.

Full Transcript

This visual execution trace shows how a SQL query filters rows based on a Boolean column named 'completed'. The query selects rows where completed is TRUE. Each row's completed value is checked against TRUE. Rows with TRUE pass the condition and are included in the result. Rows with FALSE or NULL do not pass and are excluded. NULL is treated as unknown and fails the filter. The trace highlights that 'WHERE completed' is equivalent to 'WHERE completed = TRUE' in PostgreSQL. This helps beginners understand how Boolean filtering works in SQL and why NULL values behave differently.

Practice

1. Which SQL condition correctly filters rows where a boolean column is_active is true?

easy

A. WHERE NOT is_active

B. WHERE is_active = FALSE

C. WHERE is_active = NULL

D. WHERE is_active = TRUE

Solution

1. Step 1: Understand boolean filtering for true values

   To filter rows where a boolean column is true, you can use WHERE column = TRUE or simply WHERE column.

2. Step 2: Evaluate each option

   WHERE is_active = TRUE uses WHERE is_active = TRUE, which is correct. WHERE is_active = FALSE filters false values. WHERE NOT is_active filters false values by negation. WHERE is_active = NULL compares to NULL, which is invalid for boolean filtering.

3. Final Answer:

   WHERE is_active = TRUE -> Option D

4. Quick Check:

   Boolean true filter = WHERE column = TRUE [OK]

Hint: Use WHERE column = TRUE or just WHERE column for true [OK]

Common Mistakes:

• Using WHERE column = FALSE to find true rows
• Using WHERE NOT column to find true rows
• Comparing boolean to NULL

2. Which of the following is the correct syntax to filter rows where the boolean column is_verified is false in PostgreSQL?

easy

A. WHERE is_verified = TRUE

B. WHERE is_verified == FALSE

C. WHERE NOT is_verified

D. WHERE is_verified = NULL

Solution

1. Step 1: Recall boolean false filtering syntax

   To filter false boolean values, use WHERE column = FALSE or WHERE NOT column.

2. Step 2: Check each option's syntax

   WHERE NOT is_verified uses WHERE NOT is_verified, which is valid and filters false values. WHERE is_verified = TRUE filters true values. WHERE is_verified == FALSE uses double equals which is invalid in SQL. WHERE is_verified = NULL compares to NULL, which is incorrect.

3. Final Answer:

   WHERE NOT is_verified -> Option C

4. Quick Check:

   Boolean false filter = WHERE NOT column [OK]

Hint: Use WHERE NOT column or WHERE column = FALSE for false [OK]

Common Mistakes:

• Using == instead of = in SQL
• Comparing boolean to NULL
• Using WHERE column = TRUE to find false rows

3. Given the table users with a boolean column is_admin, what will the following query return?

SELECT username FROM users WHERE is_admin;

medium

A. All usernames where is_admin is true

B. All usernames where is_admin is false

C. All usernames regardless of is_admin value

D. Syntax error due to missing comparison

Solution

1. Step 1: Understand filtering by boolean column directly

   In PostgreSQL, using WHERE is_admin filters rows where is_admin is true.

2. Step 2: Analyze the query result

   The query selects usernames only where is_admin is true, so it returns those usernames.

3. Final Answer:

   All usernames where is_admin is true -> Option A

4. Quick Check:

   WHERE boolean_column filters true rows [OK]

Hint: WHERE boolean_column filters true rows directly [OK]

Common Mistakes:

• Thinking WHERE boolean_column filters false rows
• Expecting syntax error without explicit comparison
• Assuming it returns all rows

4. Identify the error in this query that tries to select rows where is_active is false:

SELECT * FROM accounts WHERE is_active = FALSE;

medium

A. No error, query is correct

B. Should use WHERE NOT is_active instead

C. Boolean columns cannot be compared with FALSE

D. Missing parentheses around FALSE

Solution

1. Step 1: Check boolean comparison syntax

   In PostgreSQL, comparing a boolean column with FALSE using = FALSE is valid syntax.

2. Step 2: Confirm query correctness

   The query correctly filters rows where is_active is false. Using WHERE NOT is_active is an alternative but not required.

3. Final Answer:

   No error, query is correct -> Option A

4. Quick Check:

   Boolean = FALSE is valid syntax [OK]

Hint: Boolean = FALSE is valid; NOT is optional [OK]

Common Mistakes:

• Thinking boolean cannot be compared with FALSE
• Believing parentheses are needed around FALSE
• Confusing NOT with = FALSE as mandatory

5. You want to select all rows from orders where the boolean column shipped is false or NULL. Which query correctly achieves this?

hard

A. SELECT * FROM orders WHERE NOT shipped;

B. SELECT * FROM orders WHERE shipped = FALSE OR shipped IS NULL;

C. SELECT * FROM orders WHERE shipped = FALSE;

D. SELECT * FROM orders WHERE shipped IS NULL;

Solution

1. Step 1: Understand boolean and NULL filtering

   Boolean columns can be true, false, or NULL. To include false or NULL, both conditions must be checked.

2. Step 2: Analyze each option

   SELECT * FROM orders WHERE shipped = FALSE OR shipped IS NULL; explicitly checks for false or NULL, correctly including both. SELECT * FROM orders WHERE NOT shipped; filters only false but excludes NULL. SELECT * FROM orders WHERE shipped = FALSE; excludes NULL. SELECT * FROM orders WHERE shipped IS NULL; filters only NULL, missing false rows.

3. Final Answer:

   SELECT * FROM orders WHERE shipped = FALSE OR shipped IS NULL; -> Option B

4. Quick Check:

   Include false and NULL with OR condition [OK]

Hint: Use OR with IS NULL to include false or NULL [OK]

Common Mistakes:

• Using NOT shipped excludes NULL rows
• Ignoring NULL values in boolean filtering
• Assuming shipped = FALSE includes NULL