Boolean column filtering patterns in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Boolean column in a database?

A Boolean column stores values that can only be true or false. It helps to represent yes/no or on/off states in data.

beginner

How do you filter rows where a Boolean column is true in PostgreSQL?

Use WHERE column_name = TRUE or simply WHERE column_name to get rows where the Boolean column is true.

beginner

How do you filter rows where a Boolean column is false in PostgreSQL?

Use WHERE column_name = FALSE or WHERE NOT column_name to get rows where the Boolean column is false.

intermediate

What happens if you filter a Boolean column with WHERE column_name IS NULL?

It returns rows where the Boolean column has no value (NULL). This is different from true or false and means the value is unknown or missing.

intermediate

Why might you use WHERE column_name IS NOT TRUE instead of WHERE column_name = FALSE?

Because IS NOT TRUE includes both false and NULL values, while = FALSE only includes false. This helps catch unknown or missing data too.

Which SQL clause filters rows where a Boolean column named 'active' is true?

A WHERE active = TRUE

B WHERE active = FALSE

C WHERE active IS NULL

D WHERE active

To filter rows where 'active' is true, use WHERE active = TRUE or simply WHERE active.

How do you select rows where a Boolean column 'subscribed' is false?

A WHERE subscribed = TRUE

B WHERE subscribed IS NULL

C WHERE NOT subscribed

D WHERE subscribed = NULL

WHERE NOT subscribed filters rows where 'subscribed' is false.

What does WHERE flag IS NULL return?

A Rows where flag is true

B Rows where flag is false

C All rows

D Rows where flag has no value

IS NULL returns rows where the Boolean column has no value (NULL).

Which clause includes rows where a Boolean column is false or NULL?

A WHERE column_name IS NOT TRUE

B WHERE column_name = FALSE

C WHERE column_name = TRUE

D WHERE column_name IS NULL

IS NOT TRUE includes both false and NULL values.

What is the simplest way to filter rows where a Boolean column 'enabled' is true?

A WHERE enabled

B WHERE enabled IS NULL

C WHERE NOT enabled

D WHERE enabled = TRUE

WHERE enabled is a shorthand for WHERE enabled = TRUE.

Explain how to filter rows based on a Boolean column in PostgreSQL for true, false, and NULL values.

Think about how true, false, and unknown values are stored and checked.

Describe why you might use 'IS NOT TRUE' instead of '= FALSE' when filtering Boolean columns.

Consider how NULL values behave in Boolean filtering.

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