intro
Let’s learn everything you need to know about the SQL BOOLEAN type in all major relational database systems.
In programming languages, the boolean data type is one of the most useful and commonly used data types. Given its importance, the same concept can be applied to data stored in a database. Still, the SQL BOOLEAN data type is not always as widely available as you might expect…
By the end of this guide, you will know whether the BOOLEAN data type exists in SQL, how to use/implement it in MySQL, PostgreSQL, SQL Server, and Oracle, and why it is important.
Let’s dive in!
Is there a BOOLEAN Data Type in SQL?
Technically yes. SQL:1999 introduced a BOOLEAN data type in SQL. Specifically, according to the ISO/IEC 9075-2:1999 standard (commonly referred to as SQL-99):
“The data type BOOLEAN comprises the distinct truth values TRUE and FALSE. Unless prohibited by a NOT NULL constraint, the BOOLEAN data type also supports the UNKNOWN truth value as the NULL value […]. [UNKNOWN and NULL] may be used interchangeably to mean exactly the same thing.”
In simpler terms, that means:
However, as is often the case, the implementation and native support of the SQL BOOLEAN data type vary widely across relational database management systems. As a result, there is not a single, universal de facto standard for how the BOOLEAN type behaves in SQL across all platforms.
SQL BOOLEAN: Syntax and Availability
Let’s explore how the BOOLEAN data type SQL mechanism is implemented—or how it can be emulated—in the major relational databases:
MySQL BOOLEAN/BOOL Type
MySQL does not provide a dedicated BOOLEAN data type. Instead, users generally implement it via the TINYINT(1) data type. This can store integers in the range -128 to 127. Yet, in practice, it is used with 0 meaning FALSE and 1 meaning TRUE. Any nonzero value will also be treated as TRUE:
1
CREATE TABLE users (
2
id INT PRIMARY KEY AUTO_INCREMENT,
3
-- ... other columns ...
4
is_active TINYINT(1) -- 1 for TRUE; -- 0 for FALSE
5
);
Note that in MySQL, the BOOLEAN and BOOL keywords are simply aliases for TINYINT(1):
1
CREATE TABLE users (
2
id INT PRIMARY KEY AUTO_INCREMENT,
3
-- ... other columns ...
4
is_active BOOLEAN -- 1 for TRUE; 0 for FALSE
5
);
It should be noted that the same can be done with values that occupy more space on the disk (e.g. SMALLINT, INT, or even BIGINT) but the best way to perform such operations is with datatypes that occupy as little space as possible, hence our example with TINYINT.
For more information, refer to our guide on the MySQL BOOLEAN data type.
PostgreSQL BOOLEAN Type
PostgreSQL implements the standard SQL BOOLEAN type. In particular, a PostgreSQL BOOLEAN column can hold three states: TRUE, FALSE, and UNKNOWN (represented by the SQL NULL value). Under the hood, a BOOLEAN is stored as a 1-byte data type.
The string representations for boolean values in PostgreSQL are:
| Boolean Value | Accepted Inputs |
|---|---|
| TRUE | TRUE, 'yes', 'on', 1 |
| FALSE | FALSE, 'no', 'off', 0 |
Note: Unique prefixes of these strings are also accepted (e.g., '``t``' for TRUE). Plus, leading or trailing whitespace is ignored, and case does not matter.
Use it as in the following example:
1
CREATE TABLE users (
2
id SERIAL PRIMARY KEY,
3
-- ... other columns ...
4
is_active BOOLEAN -- TRUE, 'yes', `on', or 1 for TRUE; FALSE, 'no', 'off', or 0 for FALSE;
5
);
SQL Server BOOLEAN Implementation
SQL Server does not provide a native BOOLEAN data type. Instead, the official documentation recommends using the BIT type to represent boolean values.
In SQL Server, the BIT type is an integer data type that can store one of three values:
Note that the string values TRUE and FALSE are converted automatically to 1 and 0 when inserted.
Use it as below:
1
CREATE TABLE users (
2
id INT PRIMARY KEY IDENTITY,
3
-- ... other columns ...
4
is_active BIT -- 1, 'TRUE' for TRUE; 0, 'FALSE' for FALSE
5
);
Oracle BOOLEAN Data Type
Starting with Oracle Database 23c (now branded as Oracle 23ai), Oracle introduced a native BOOLEAN data type.
According to the documentation:
“The data type BOOLEAN has the truth values TRUE and FALSE. If there is no NOT NULL constraint, the BOOLEAN data type also supports the truth value UNKNOWN as the NULL value.”
In other words, Oracle 23c’s implementation closely follows the SQL standard.
You can use the SQL keywords TRUE, FALSE, and NULL directly to represent these states:
1
CREATE TABLE users (
2
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3
-- ... other columns ...
4
is_active BOOLEAN
5
);
Where the possible values are:
Represents TRUE | Represents FALSE |
|---|---|
TRUE | FALSE |
'true' | 'false' |
'yes' | 'no' |
'on' | 'off' |
1 (or any other non-0 number, including negative numbers) | 0 |
't' | 'f' |
'y' | 'n' |
Note: Before Oracle Database 23c/Oracle 23ai, there was no native BOOLEAN type in Oracle SQL. Developers typically emulated it using:
When to Use a Boolean in SQL
An SQL BOOLEAN column is ideal whenever a field represents a binary choice: true/false, yes/no, on/off. In other words, common use cases include:
Relying on boolean values is also useful when dealing with conditional expressions in SQL. You can directly evaluate conditions, store the result in a BOOLEAN (or equivalent) column, and use those values in queries for filtering.
To better understand how to use the BOOLEAN data type in SQL, consider this PostgreSQL example. Assume you have the following products table, which has this SQL DDL definition:
This table contains some sample products:
Note: See how a feature-rich PostgreSQL client like DbVisualizer makes exploring SQL DDL and inspecting data much easier.
If you are only interested in retrieving products that are currently in stock, you could write this query:
1
SELECT product_name, price, in_store
2
FROM products
3
WHERE in_store = TRUE;
The result will be:
Awesome! The BOOLEAN data type SQL mechanism now has no secrets for you.
Boolean Type SQL Summary Table
| RDBMS | Native BOOLEAN Implementation | Implementation / Alias | Supported Values | Notes |
|---|---|---|---|---|
| MySQL | No | TINYINT(1); BOOLEAN and BOOL are aliases | 0 = FALSE, 1 (or any nonzero) = TRUE | Emulates Boolean with integer; NOT NULL forbids 0/1 only |
| PostgreSQL | Yes | BOOLEAN | TRUE, FALSE, UNKNOWN (NULL) + strings 'yes', 'no', 'on', 'off', 't', 'f', 'y', 'n', numbers 1/0 | Fully follows SQL standard; stored as 1 byte |
| SQL Server | No | BIT | 0 = FALSE, 1 = TRUE, NULL = UNKNOWN | Strings 'TRUE'/'FALSE' convert automatically |
| Oracle | Yes (since Oracle 23ai) | BOOLEAN | TRUE, FALSE, UNKNOWN (NULL) + same string/number conventions as PostgreSQL | Before 23c, emulated via NUMBER(1,0) or CHAR(1) |
Conclusion
In this blog post, you learned about the BOOLEAN data type in SQL. You explored why not all databases implement it the same way and how to use or emulate it in MySQL, PostgreSQL, SQL Server, and Oracle.
As shown here, a tool like DbVisualizer makes it easier to explore data and write SQL queries. Its smart auto-complete and type-aware features support all SQL data types, including BOOLEAN (when available):
With support for over 50 databases, it equips you with powerful features like SQL formatting, ER diagram generation, query optimization tools, and more. Download DbVisualizer for free today!
FAQ
How to get a boolean result from a SQL query?
You can get a boolean from a SQL query by using a comparison or conditional expression that evaluates to TRUE or FALSE. For example, in PostgreSQL:
1
SELECT full_name, (age >= 18) AS is_adult
2
FROM users;
This query returns TRUE if age is 18 or older, otherwise FALSE :
In databases like MySQL, SQL Server, or Oracle (pre-23c), the result may be returned as 1/0 (TINYINT or BIT):
Is BOOLEAN in SQL part of the ISO/ANSI standard?
Yes, the BOOLEAN data type is part of the SQL:1999 (SQL-99) ISO/ANSI standard. It defines BOOLEAN with three possible states: TRUE, FALSE, and UNKNOWN (represented by NULL). However, not all relational databases implement it natively. Some, like PostgreSQL and Oracle 23c, support it directly, while others, such as MySQL and SQL Server, emulate it using TINYINT(1) and BIT, respectively.
What is the difference between the BOOLEAN and BOOL datatype in SQL?
In the SQL databases that support both the BOOLEAN and BOOL data types, there is no functional difference between the two. Both represent the same concept—a boolean value (TRUE, FALSE, and optionally UNKNOWN). Thus, in some databases (e.g. MySQL), BOOL is just an alias for BOOLEAN, which in turn can be an alias for another data type (e.g., TINYINT(1)).
How to represent the bool value in SQL?
In SQL, boolean values are generally represented using the keywords TRUE and FALSE. If the column allows NULL, it can also represent an unknown state. In databases without a native BOOLEAN type, boolean values are typically emulated using integers or bits: 1 (or any nonzero) for TRUE, 0 for FALSE. Other literal values with boolean meaning may also be available.
Why use a visual database client?
A visual database client like DbVisualizer makes database management much easier. That is because it allows you to efficiently explore, visualize, and query data. In particular, DbVisualizer provides an intuitive UI for handling tables, navigating schema relationships, and writing or debugging SQL in a powerful built-in SQL editor. Other available features are autocomplete, ER diagrams, and advanced data export tools. Try the Pro version with a 21-day free trial!
