SQL Boolean and NULL Handling | Full Beginner’s Guide

Introduction

n SQL, dealing with truth values and missing data is very important for building reliable applications. SQL provides special ways to manage

• Boolean values (True/False logic)
• NULL values (Unknown or missing information)

In this section, you’ll learn how BOOLEAN types work and how to handle NULL values effectively.

1. Boolean Values in SQL

In standard SQL, BOOLEAN is a data type that can hold only two values:

• TRUE
• FALSE

Some SQL databases (like MySQL) do not have a real BOOLEAN type — they simulate it using TINYINT(1), where:

• 0 means FALSE
• 1 means TRUE

Syntax:

column_name BOOLEAN;

or (in systems like MySQL):

column_name TINYINT(1);

Example:

CREATE TABLE users (
id INT,
is_active BOOLEAN
     );

Inserting values:

INSERT INTO users (id, is_active) VALUES (1, TRUE);
            INSERT INTO users (id, is_active) VALUES (2, FALSE);

Selecting boolean values:

SELECT * FROM users WHERE is_active = TRUE;

Important Notes:

• In MySQL, BOOLEAN is just an alias for TINYINT(1).
• In PostgreSQL, BOOLEAN is a real data type.
• Always prefer TRUE and FALSE instead of 1 and 0 for better readability.

2. NULL Values in SQL

NULL represents missing, unknown, or undefined data in SQL. It does not mean zero (0) or an empty string ('') — it means no value at all.

Syntax:

When creating a table, columns are nullable by default unless specified otherwise.

column_name data_type NULL;

or

column_name data_type NOT NULL;

Example:

CREATE TABLE customers (
id INT,
email VARCHAR(100) NULL
    );

Here, email can be NULL if the customer hasn't provided an email address.

Checking NULL Values

Since NULL is not equal to anything, you can't use = NULL or != NULL. Instead, use:

• IS NULL
• IS NOT NULL

Example:

SELECT * FROM customers WHERE email IS NULL;
              

This query fetches all customers without an email address.

SELECT * FROM customers WHERE email IS NOT NULL;

This fetches all customers who have provided an email.

Handling NULL in Queries

You can use functions to better manage NULLs:

• COALESCE(value1, value2, ...) – Returns the first non-NULL value.
• IFNULL(expression, alternative) – MySQL function to replace NULL.

Example with COALESCE:

SELECT name, COALESCE(email, 'No email provided') AS contact_email
FROM customers;

Example with IFNULL:

SELECT name, IFNULL(phone, 'Unknown') AS contact_number
FROM customers;

Quick Comparison: BOOLEAN vs NULL

Best Practices

• Use BOOLEAN columns for clear yes/no questions (active/inactive, verified/unverified).
• Always check for NULL using IS NULL and IS NOT NULL, never with = NULL.
• Use DEFAULT values where appropriate to minimize NULLs if they don't make sense for your data.
• Handle NULLs properly when displaying data to users to avoid confusion (e.g., "Unknown" instead of blank fields).

Conclusion

Understanding BOOLEAN values and NULL handling is crucial for clean, accurate, and reliable SQL databases.