SQL Boolean Data Type
Introduction
A Boolean represents a logical condition such as true or false. In a database it can describe whether an account is active, an email is verified, a feature is enabled, or a record is visible. Boolean columns are compact and readable when the business fact is genuinely binary, but poor Boolean design can hide missing information, create contradictory states, and make future workflows difficult to extend.
SQL adds an important complication: nullable logical expressions can evaluate to UNKNOWN. In addition, database products do not all implement one identical BOOLEAN column type. PostgreSQL has a native Boolean, SQL Server commonly uses BIT, MySQL treats BOOLEAN as an alias for a numeric type, SQLite relies on integer conventions, and Oracle behavior depends on release. Sound design therefore separates logical meaning from physical representation.
What Is a Boolean?
A Boolean value answers a yes-or-no proposition. The values are usually written as TRUE and FALSE. A column should represent one clearly named proposition so each row can be read naturally.
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
has_verified_email BOOLEAN NOT NULL DEFAULT FALSE
);
The physical syntax must be adapted for databases without native BOOLEAN, but the intended domain remains two logical states.
Boolean Columns Represent State
A Boolean stores the state of one independent fact at a point in time. Common examples include is_active, is_public, has_access, can_retry, and should_notify. The name should make both values understandable: when is_active is false, the account is not active.
A vague name such as flag or status_boolean forces readers to search for meaning. Positive names are usually easier than negative names because expressions such as NOT is_not_disabled are error-prone.
Naming Conventions
Prefixes communicate the kind of proposition. is_ describes a state, has_ describes possession or existence, can_ describes capability, and should_ describes an instruction or policy. These conventions are guidelines rather than SQL requirements.
| Name | Question it answers | Typical meaning |
|---|---|---|
is_archived | Is the record archived? | Lifecycle state |
has_attachment | Does it have an attachment? | Existence |
can_publish | Can the subject publish? | Capability |
should_retry | Should processing retry? | Decision |
Confirm that a stored value is authoritative. If has_attachment can be derived cheaply and reliably from related rows, storing it can create inconsistency.
Inserting and Filtering Boolean Values
INSERT INTO users (user_id, is_active, has_verified_email)
VALUES (101, TRUE, FALSE);
SELECT user_id
FROM users
WHERE is_active = TRUE;
UPDATE users
SET has_verified_email = TRUE
WHERE user_id = 101;
Some products allow predicate shorthand such as WHERE is_active, while others require comparison to a BIT or numeric value. Explicit syntax can improve portability when the physical type is not a native Boolean.
Defaults and NOT NULL
A default supplies a value when an insert omits the column; it does not overwrite an explicitly supplied NULL. NOT NULL is needed when UNKNOWN has no domain meaning. Defaults should reflect a real workflow rule rather than silently guessing.
is_enabled BOOLEAN NOT NULL DEFAULT FALSE
For a new feature opt-in, false may be a sensible default. For a compliance decision that must be reviewed, a default false could incorrectly imply that review occurred.
Nullable Booleans and Three-Valued Logic
SQL logical expressions can be TRUE, FALSE, or UNKNOWN. A nullable Boolean column uses NULL for the third state. NULL does not mean false; it means absent, unknown, or not applicable according to the column's documented semantics.
Three states can be valid, such as an approval that has not yet been decided. However, a nullable Boolean often hides a real status domain such as pending, approved, and rejected. If each state has a business name and workflow, a status column or related entity is clearer.
Testing TRUE, FALSE, and UNKNOWN
Standard logical predicates include IS TRUE, IS FALSE, and IS UNKNOWN, though support and preferred syntax differ by product. NULL must be tested with IS NULL, never = NULL.
WHERE approved IS TRUE
WHERE approved IS FALSE
WHERE approved IS UNKNOWN
WHERE approved IS NULL
IS NOT TRUE includes false and unknown, which differs from IS FALSE. That distinction matters in access-control and compliance queries.
How WHERE Treats UNKNOWN
A WHERE clause retains only rows for which its predicate evaluates to TRUE. Rows producing FALSE or UNKNOWN are filtered out. Therefore, WHERE NOT approved may exclude NULL approvals rather than treating them as false.
Write predicates from the exact domain requirement. To include every row not explicitly true, use a supported form equivalent to approved IS NOT TRUE rather than relying on intuition.
NOT, AND, and OR With NULL
| Expression | Result | Reason |
|---|---|---|
NOT UNKNOWN | UNKNOWN | The missing truth value remains unknown |
FALSE AND UNKNOWN | FALSE | FALSE determines the conjunction |
TRUE AND UNKNOWN | UNKNOWN | The second value can change the result |
TRUE OR UNKNOWN | TRUE | TRUE determines the disjunction |
FALSE OR UNKNOWN | UNKNOWN | The missing value can change the result |
Parenthesize combined conditions when precedence might be unclear, especially when NULL is possible. Tests should cover every meaningful truth-value combination.
Native Boolean Versus Numeric Flag
A native BOOLEAN communicates logical intent and limits values to the Boolean domain. A numeric flag typically uses zero and one, but an unconstrained integer may also accept 2, -1, or other values. If a product requires a numeric representation, add a constraint.
is_active SMALLINT NOT NULL DEFAULT 1
CHECK (is_active IN (0, 1))
The application should expose this as a Boolean rather than leaking storage details into business code.
Boolean Versus Character Flag
Legacy systems often store 'Y' and 'N'. Character flags are readable in raw output but invite case differences, spaces, and alternate values such as 'T', 'F', or blank. They also require translation in application and API layers.
is_active CHAR(1) NOT NULL
CHECK (is_active IN ('Y', 'N'))
Use a native logical type when available. Retain character flags only for a real compatibility requirement and constrain them strictly.
PostgreSQL BOOLEAN
PostgreSQL provides a native BOOLEAN type with TRUE, FALSE, and NULL. Queries can use direct predicates, NOT, and IS TRUE forms. PostgreSQL accepts several textual input forms, but canonical SQL and application code should use clear typed values.
SELECT user_id
FROM users
WHERE is_active AND has_verified_email;
Partial indexes are especially useful for a small Boolean subset, such as active jobs that need processing.
MySQL BOOLEAN
MySQL treats BOOLEAN and BOOL as aliases for TINYINT(1). TRUE and FALSE correspond to 1 and 0, but the alias alone does not necessarily prevent other integer values. Add a check constraint where supported and ensure the application writes only canonical values.
Do not infer a strict logical domain solely from the displayed TINYINT(1) metadata. Test migration tools and drivers because some detect it as Boolean while others expose it numerically.
SQL Server BIT
SQL Server uses BIT for Boolean-like columns. BIT stores 0, 1, or NULL and can pack several BIT columns efficiently. Filters commonly compare the column with 1 or 0 rather than using it directly as a predicate.
SELECT user_id
FROM users
WHERE is_active = CAST(1 AS bit);
Application drivers generally map BIT to Boolean types, but nullable BIT requires a nullable application representation.
Oracle and SQLite Boolean Storage
Modern Oracle releases support BOOLEAN in SQL contexts, while older Oracle schemas commonly use NUMBER(1) or CHAR(1) with constraints. Migration plans must target the actual deployed release and driver.
SQLite has no separate strict Boolean storage class. TRUE and FALSE are recognized as numeric values, and Boolean declarations use integer affinity. Add CHECK (column IN (0,1)) when strict domain enforcement is required.
Cross-Database Representation
| Database | Common representation | Important note |
|---|---|---|
| PostgreSQL | BOOLEAN | Native logical type |
| MySQL | BOOLEAN/TINYINT(1) | Alias needs domain awareness |
| SQL Server | BIT | Filter commonly uses 1 or 0 |
| Oracle | BOOLEAN in modern releases; legacy flags | Release-dependent |
| SQLite | Integer convention | Use checks for strict values |
Portable applications should expose a logical domain while adapting DDL and query syntax to each database.
Boolean Versus Status
A Boolean is correct only for two meaningful states. A payment is rarely just paid or unpaid; it may be pending, authorized, captured, failed, refunded, or cancelled. Multiple Booleans for those states can contradict each other.
payment_status VARCHAR(20) NOT NULL
CHECK (payment_status IN
('PENDING', 'AUTHORIZED', 'PAID', 'FAILED', 'REFUNDED'))
A status domain supports workflow transitions and future states more naturally than a growing collection of flags.
Avoid Contradictory Flags
Columns such as is_pending, is_paid, and is_refunded can all become true unless complex constraints prevent it. They duplicate one underlying state. Store one authoritative status and derive convenience predicates in queries or application models.
Also avoid redundant inverse pairs such as is_active and is_inactive. One value always determines the other and creates an unnecessary consistency problem.
Boolean Versus Timestamp
A Boolean answers whether something happened, while a timestamp answers whether and when. For email verification, deletion, publication, or acceptance, a nullable timestamp can carry more useful information.
email_verified_at TIMESTAMP NULL
-- Derived predicate:
email_verified_at IS NOT NULL
If the exact time matters for audit, retention, ordering, or user display, the timestamp should be authoritative. A separate Boolean may become inconsistent unless maintained automatically for a proven performance need.
Stored Versus Derived Booleans
A Boolean should be stored when it represents an independent decision or state that cannot be reliably derived. It should be computed when authoritative columns already determine it. has_orders can come from related orders; is_expired can come from expires_at and current time.
Time-dependent Booleans become stale without scheduled updates. Derivation, a view, generated logic, or a targeted cache with explicit invalidation is usually safer.
Boolean Expressions and CASE
Comparisons such as amount > 0 produce logical results. AND combines required conditions, OR accepts alternatives, and NOT reverses truth while preserving UNKNOWN. CASE can map logical results into a database-specific representation or display label.
SELECT order_id,
CASE WHEN paid_at IS NOT NULL THEN 'PAID'
ELSE 'UNPAID'
END AS payment_label
FROM orders;
Do not store a CASE result merely because it is convenient to display if the source remains authoritative.
Boolean Aggregation
Databases provide different direct Boolean aggregate support, but conditional aggregation is portable in concept. It can count true states, calculate percentages, or check whether any qualifying row exists.
SELECT COUNT(*) AS total_users,
SUM(CASE WHEN is_active = TRUE THEN 1 ELSE 0 END) AS active_users
FROM users;
Adapt comparison syntax to the database. Define whether NULL belongs in the denominator before calculating percentages.
Indexing Boolean Columns
A standalone Boolean has at most a few distinct values, so a normal index can have low selectivity. If almost every row is active, an index on is_active may not help a query for active rows. A query for the rare false subset can still benefit.
Composite indexes can be useful when Boolean state is combined with tenant, category, or time. Column order should follow actual predicates and distribution, not a universal rule about putting the Boolean first.
Partial and Filtered Indexes
Some databases can index only rows matching a predicate. A PostgreSQL partial index or SQL Server filtered index can efficiently support a small actionable subset.
CREATE INDEX idx_jobs_ready
ON jobs (scheduled_at)
WHERE is_ready = TRUE;
This can be more useful than indexing the flag across the whole table. The query predicate must match the indexed condition, and portability requires product-specific DDL.
Boolean Constraints
A native Boolean already restricts non-NULL values to true and false. Numeric and character emulations need CHECK constraints. NOT NULL controls missingness, while DEFAULT controls omitted input; neither substitutes for a domain constraint on a weak flag type.
Cross-column rules can also use checks, such as requiring a verification timestamp when a legacy verification flag is true during migration. Keep such duplication temporary when possible.
Application Language Mappings
Java primitive boolean cannot represent SQL NULL, while wrapper Boolean can. Python bool values map naturally but still need None for NULL. JavaScript booleans are distinct from numbers and strings.
Never treat the nonempty string "false" as a Boolean; in many languages it is truthy. Drivers and data-access layers should use typed getters, setters, and parameters rather than parsing display text.
JSON and API Contracts
JSON has literal true and false values. APIs should send them as Booleans rather than "true", "false", "Y", or undocumented 1 and 0 values. A nullable field needs a clear distinction among missing, explicit null, false, and true.
{
"active": true,
"emailVerified": false
}
API schemas should state requiredness and default behavior. Database defaults do not automatically apply to partial update semantics.
ORM Mapping and Portability
ORMs often map one Boolean property to BOOLEAN, BIT, TINYINT, or another representation based on dialect. Verify generated DDL, NULL handling, default expressions, and migration diffs. A primitive application property can accidentally convert database NULL into false or fail during materialization.
Keep business code logical and isolate storage conversion in the persistence layer. Integration tests are necessary because in-memory tests may not reproduce database three-valued logic.
COALESCE and Missing Meaning
COALESCE(flag, FALSE) is useful only when the business rule explicitly treats unknown as false for that operation. It does not change the stored meaning and can hide incomplete data if applied routinely.
Prefer NOT NULL when unknown is invalid. If unknown is valid, give it a clear interpretation or model the richer status directly.
UI Checkboxes and Toggle Controls
A checkbox naturally maps to a required Boolean when checked and unchecked are the only states. Nullable fields require a three-state control or another explicit interaction; an ordinary checkbox cannot communicate unknown reliably.
Toggle operations should update to a requested state rather than blindly invert the current value in distributed systems. Retries of an inversion are not idempotent, while setting is_enabled = TRUE is.
Permissions and Feature Flags
One independent capability can use a Boolean, but real permission systems often require roles, resources, scopes, inheritance, denial rules, and audit history. A table full of can_* columns becomes difficult to extend and reason about.
Feature flags can begin as Booleans but may evolve into percentage rollout, audience rules, schedules, or variants. Use a richer feature-configuration model when those requirements exist.
Soft Delete Design
is_deleted supports simple filtering, but deleted_at answers when deletion occurred and derives the Boolean through NULL. Audit requirements may also need deleted_by and reason.
Every normal query must exclude deleted rows correctly, and uniqueness rules may need to apply only to active rows. Partial indexes or dedicated archival strategies can help, depending on the database.
Availability and Payment Flags
Availability often depends on stock, reservations, schedule, region, and policy, so a stored is_available flag can become stale. Payment state is usually multi-stage. These are signals to model authoritative facts and derive a current answer rather than adding a convenient Boolean.
Store a flag only when it is itself an independent business decision, such as an administrator explicitly enabling a product despite other data.
Auditability and Change History
A current Boolean does not show who changed it, when, or why. Important decisions such as approval, suspension, publication, and consent often require timestamp, actor, reason, and possibly a history table.
A good audit model can keep the Boolean for current-state querying while recording transitions separately. Ensure both are changed atomically so the history and current state cannot disagree.
Cardinality and Query Planning
Boolean distribution may be balanced or highly skewed. Query optimizers use statistics to estimate selectivity, and those statistics can become stale after bulk updates. One environment's index behavior may not match another with different data distribution.
Inspect real execution plans and update statistics. Do not accept myths that Boolean indexes are always useless or always beneficial.
Uniqueness and Cross-Row Rules
A unique constraint on a Boolean alone permits at most one true and one false row, which is rarely useful. Booleans can participate in composite or filtered uniqueness, such as allowing one primary address per customer.
Enforcing exactly one flagged row within a group may require a partial unique index, constraint strategy, or transaction-safe service operation. The Boolean column alone cannot enforce the cross-row business rule.
Sorting, Joins, and HAVING
Boolean sort order differs by product and representation, so use explicit CASE expressions when display order matters. Joins can include logical state, but placing filters in ON versus WHERE changes outer-join behavior. HAVING can filter aggregated logical conditions through product-specific or conditional expressions.
Keep queries readable and test NULL cases. A predicate that seems obvious under two-valued logic can change when UNKNOWN enters an outer join.
CHECK Constraints and UNKNOWN
A SQL CHECK constraint generally rejects rows when the expression is false but can allow UNKNOWN. Therefore, CHECK (flag = TRUE) does not necessarily replace NOT NULL. Use both when the column must be present and true.
This difference between WHERE and CHECK behavior is a common source of mistakes. Test NULL explicitly in schema validation.
Migration From Legacy Flags
Before converting Y/N, 1/0, or free-form flags, profile every distinct value, including spaces, case variants, NULL, and unexpected numbers. Define a mapping for each value and quarantine records with ambiguous meaning.
Update application mappings, reports, APIs, indexes, and ETL jobs with the database migration. During a dual-write transition, add reconciliation checks and remove the old representation promptly to avoid divergence.
State Transitions and Invariants
A Boolean column describes current state but does not define which transitions are legal. An account might move from inactive to active only after verification, or a published record might require an author and publication timestamp. Those invariants must be enforced by a transaction, constraint, trigger, or service boundary appropriate to the architecture.
CHECK (
is_published = FALSE
OR published_at IS NOT NULL
)
When a transition has several steps, update all dependent columns atomically. A row should not briefly show is_published = TRUE without the required timestamp or content. For complex workflows, a status-transition model is generally clearer than increasingly elaborate checks around several flags.
Document who can perform each transition and whether it is reversible. A technical ability to set a Boolean back to false does not imply that the business process permits reversal.
Concurrent Updates and Idempotency
A read-then-toggle operation can lose updates when two requests read the same value and both write its inverse. It is also unsafe under retries: repeating the request reverses the state again. APIs and jobs should generally request an explicit target such as enable or disable rather than toggle.
When concurrency matters, include a row version, compare-and-set predicate, or database locking strategy. For example, an update can require both the expected current state and version before changing the row. The application must detect when no row was updated and decide whether to retry, report a conflict, or accept that the requested final state already exists.
Bulk state changes need the same care. Record the selection criteria and affected count, and avoid relying on a timestamp or Boolean alone as an ordering guarantee. A transaction should keep the state change and its audit entry consistent.
Booleans in Authorization
Authorization rules are security boundaries, so nullable or contradictory flags can have serious consequences. A predicate such as is_admin OR can_delete must define how NULL behaves and must not use COALESCE casually. In deny-by-default systems, unknown capability should not accidentally become permission.
Simple roles can contain Boolean capabilities, but permissions often depend on resource ownership, tenant, scope, time, and policy. Store normalized grants or policy data when the domain exceeds independent yes-or-no facts. Avoid copying an authorization result into many rows where it can become stale after a role changes.
Test unauthorized, authorized, suspended, missing, and conflicting states. Integration tests should exercise the actual database query because mock logic may use two-valued language Booleans and miss SQL UNKNOWN behavior.
Consent and Compliance Evidence
A Boolean such as marketing_opt_in is useful for current preference, but many regulations and audit processes require when consent was given, which policy version applied, how it was collected, and when it was withdrawn. One flag cannot provide that evidence.
A consent event or history table can record subject, purpose, decision, timestamp, source, and policy version. The current Boolean may be maintained as a query-friendly projection, provided it is derived transactionally from authoritative events. Different purposes should not be collapsed into one broad consent flag.
Defaults are especially important here. Defaulting a consent field to true can violate both user expectations and policy. Unknown, not asked, declined, granted, and withdrawn may form a multi-state lifecycle that deserves explicit modeling.
Bulk Imports and Data Exchange
Imports often deliver Boolean concepts as Y/N, yes/no, 1/0, empty fields, or localized words. Define an accepted mapping per source instead of writing one permissive parser that treats every unfamiliar nonempty value as true. Reject and report values outside the contract.
Exports should publish one canonical representation. CSV has no native Boolean type, so its data dictionary must specify exact values and NULL handling. JSON does have native Booleans and should use them. Spreadsheet exports need care because tools may convert text and numbers inconsistently.
Track counts by mapped value, missing value, and rejection reason. A sudden increase in unknown input can reveal an upstream format change before it corrupts the logical domain.
Reporting and Analytical Models
Reports often group Boolean state into labels and calculate rates. Define the denominator explicitly: an approval rate can exclude unknown rows, include them as not approved, or display a separate pending category. Each choice answers a different business question.
Analytical systems sometimes encode Booleans as 0 and 1 for aggregation or machine learning. That representation is convenient but should be derived with documented NULL handling. A missing fact is not automatically zero. Preserve the original logical domain or status alongside engineered features when traceability matters.
Snapshot reporting also requires time context. The current is_active value cannot answer how many accounts were active last month unless history, effective periods, or periodic snapshots exist.
Schema Evolution From Boolean to Status
Requirements frequently outgrow a Boolean. An is_approved field may later need pending, rejected, expired, and revoked. A safe migration adds the status column, backfills it from the Boolean and related evidence, updates writers, compares both representations, switches readers, and finally removes the old flag.
The mapping is not always one-to-one. NULL may mean pending in some rows and missing data in others. Audit timestamps and related records may be needed to classify historical data. Do not force ambiguous rows into a state without recording the migration assumption.
Designing the initial Boolean with clear naming and audit context makes future evolution easier. It also reveals early when the domain already contains more than two states.
Real-World User Schema
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
email_verified_at TIMESTAMP NULL,
suspended_at TIMESTAMP NULL,
marketing_opt_in BOOLEAN NOT NULL DEFAULT FALSE
);
Active state and marketing consent are independent binary facts. Verification and suspension use timestamps because timing and audit context matter. Product-specific DDL should adapt the BOOLEAN declarations.
Operational Monitoring
Monitor invalid numeric or character flag values, unexpected NULLs, contradictory legacy flags, and differences between stored and derived state. A sudden distribution change can indicate a failed default, import mapping, or application deployment.
For cached or derived Booleans, run reconciliation against authoritative data and record refresh latency. For audit-sensitive values, alert when state changes without corresponding actor or history information.
Common Boolean Mistakes
- Using a Boolean for a workflow that has several named states.
- Treating NULL as equivalent to false without a domain rule.
- Creating several flags that can contradict one another.
- Storing an inverse flag that duplicates another column.
- Storing a derived or time-dependent Boolean without synchronization.
- Using unconstrained integers or characters as logical values.
- Sending string or numeric pseudo-Booleans in JSON.
- Assuming every database implements BOOLEAN identically.
- Indexing a low-selectivity flag without examining the workload.
- Using a Boolean when a timestamp or audit event is required.
- Blindly toggling state in retryable distributed operations.
- Expecting a default to reject explicit NULL.
Boolean Selection Checklist
- Confirm the domain has exactly two meaningful states.
- Decide whether unknown is valid and document its meaning.
- Determine whether the value is independent or derivable.
- Consider whether future requirements will add states.
- Ask whether when, who, or why must be recorded.
- Choose the database's strongest logical representation.
- Add NOT NULL, defaults, and checks deliberately.
- Map to typed application and JSON Booleans.
- Design indexes from distribution and query patterns.
- Test TRUE, FALSE, NULL, invalid values, and migrations.
Testing Boolean Behavior
Test omitted values, explicit true and false, explicit NULL, invalid numeric or character values, and default behavior. Exercise NOT, AND, OR, IS NULL, and product-specific truth predicates. Include every nullable combination in important authorization or compliance logic.
Verify application primitive and nullable mappings, ORM-generated DDL, JSON serialization, partial updates, indexes, migrations, and concurrency. A Boolean feature is not complete until the same logical value survives the full database-to-client round trip.
Interview Questions and Answers
What values can SQL Boolean logic have? TRUE, FALSE, and UNKNOWN when NULL participates.
Is NULL the same as false? No. NULL represents missing or unknown information.
Does every database support native BOOLEAN? No. Products use BOOLEAN, BIT, numeric aliases, or conventions.
When should status replace a Boolean? When the domain has more than two named states or is likely to gain them.
When is a timestamp better than a Boolean? When the system needs to know whether and when an event occurred.
Should a Boolean column be indexed? Only when distribution and query patterns make the index selective or a filtered subset useful.
Why does CHECK not always replace NOT NULL? A CHECK can accept UNKNOWN, while NOT NULL directly prohibits missing values.
Key Takeaway
A Boolean is best for one independent proposition with two meaningful states. Nullable Boolean columns introduce SQL's third truth value, UNKNOWN, which must be modeled and queried deliberately. Multi-state workflows, derived facts, and auditable events often need status, calculation, or timestamp designs instead.
BOOLEAN DESIGN = TRULY BINARY DOMAIN
+ EXPLICIT NULL MEANING
+ STRONG DATABASE REPRESENTATION
+ TYPED APPLICATION AND API MAPPING
+ AUDIT AND QUERY REQUIREMENTS
Prefer native logical types where available, constrain legacy flags, use clear positive names, and test all truth states. Keep the physical representation database-specific while preserving one consistent logical contract throughout the system.