SQL - BOOLEAN (BIT) Operator

SQL Boolean Data Type

The SQL Standard BOOLEAN Type

The standard SQL BOOLEAN data type represents truth values. It can hold one of three states: TRUE, FALSE, or UNKNOWN (represented by NULL). This is known as three-valued logic and is fundamental to how SQL handles conditions.

This data type is perfect for columns that represent a binary state, such as is_active, has_been_shipped, or is_premium_member.

Implementations in Popular Databases

While the SQL standard defines a BOOLEAN type, not all databases implement it in the same way. Understanding the differences is key to writing portable and correct code.

PostgreSQL: The Standard-Bearer

PostgreSQL provides a true BOOLEAN data type. It accepts literals like TRUE, true, 't', '1' for true, and FALSE, false, 'f', '0' for false. It is the most compliant and straightforward implementation.

CREATE TABLE feature_flags (
    flag_name VARCHAR(50) PRIMARY KEY,
    is_enabled BOOLEAN NOT NULL DEFAULT FALSE
);

MySQL: An Alias for TINYINT

MySQL does not have a native BOOLEAN type. Instead, it provides BOOL and BOOLEAN as aliases for TINYINT(1). Internally, it treats 0 as FALSE and any non-zero integer as TRUE. The keywords TRUE and FALSE are simply aliases for 1 and 0.

CREATE TABLE settings (
    setting_id INT PRIMARY KEY AUTO_INCREMENT,
    send_notifications BOOLEAN DEFAULT TRUE
);
-- This is internally converted to:
-- send_notifications TINYINT(1) DEFAULT 1

SQL Server: The BIT Type

Microsoft SQL Server uses the BIT data type to represent boolean values. It can store 1 (for true), 0 (for false), or NULL.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    IsDiscontinued BIT NOT NULL DEFAULT 0
);

Inserting and Filtering Boolean Data

When inserting and querying, it is a best practice to use the boolean literals (TRUE, FALSE) directly. This makes your code more readable and portable, even if the underlying system converts them to numbers.

Inserting Values

-- This works across PostgreSQL, MySQL, and SQL Server (with compatible settings)
INSERT INTO feature_flags (flag_name, is_enabled) VALUES ('new_dashboard', TRUE);
INSERT INTO Products (ProductID, ProductName, IsDiscontinued) VALUES (101, 'Chai', FALSE);

Filtering Data

The most readable and robust way to filter boolean columns is by using the IS operator. Avoid comparing directly to 1 or 0.

-- Good practice: Clear and explicit
SELECT * FROM feature_flags WHERE is_enabled IS TRUE;

-- Also common, works on all major RDBMS
SELECT * FROM Products WHERE IsDiscontinued = FALSE;

-- Bad practice: Less readable and can be ambiguous with NULLs
SELECT * FROM Products WHERE IsDiscontinued = 0;

Negating Conditions

To find records that are not true, you can use IS NOT TRUE or simply query for IS FALSE.

-- Find all products that are NOT discontinued
SELECT * FROM Products WHERE IsDiscontinued IS FALSE;

-- Find all feature flags that are NOT enabled (includes FALSE and NULL)
SELECT * FROM feature_flags WHERE is_enabled IS NOT TRUE;

Handling NULL in Boolean Contexts

NULL in a boolean context means ‘unknown’. This is distinct from FALSE. For example, a has_shipped column could be TRUE (it shipped), FALSE (it has not shipped), or NULL (we don’t know the shipping status yet).

To specifically find rows where the boolean state is unknown, you must use IS NULL.

-- Find orders with an unknown shipping status
SELECT * FROM Orders WHERE has_shipped IS NULL;

-- Find orders where the status is known (either TRUE or FALSE)
SELECT * FROM Orders WHERE has_shipped IS NOT NULL;

Best Practices

• Use the most appropriate type: Use BOOLEAN in PostgreSQL, BOOLEAN/TINYINT(1) in MySQL, and BIT in SQL Server.
• Prefer IS TRUE / IS FALSE: Use these operators in your WHERE clauses for maximum clarity and correctness, especially when NULL values are possible.
• Name columns clearly: Use prefixes like is_, has_, or can_ (e.g., is_active, has_subscription) to make the boolean nature of the column obvious.
• Set a NOT NULL DEFAULT: For most boolean columns, NULL is not a useful state. It’s often better to define them as NOT NULL with a sensible default, like DEFAULT FALSE.