SQL Boolean Data Type

Booleans are a universal data type among significant programming languages. They allow you to store true or false values.

Unlike other programming languages, there is no Boolean type in Standard SQL. This article will cover how we can execute a Boolean type in various SQL database engines.

Which Database Engines Support Boolean?

The Boolean data type is not available natively in Standard SQL. However, various database engines implement the Boolean type natively.

These includes:

PostgreSQL
Google BigQuery

Other database engines such as Oracle, SQL Server, and MySQL do not provide a native Boolean type.

Using Boolean in SQL Server

To store Boolean values, we can use a BIT data type.

A bit type is used to store values from 1 t0 64. Hence, we can use a bit value of 0 to represent false and 1 to describe true.

Consider the example query below that creates a table with a Boolean column using a bit type.

CREATE TABLE TABLE_NAME(
id INT NOT NULL,
first_name VARCHAR(255),
available BIT
);

The available column is a type bit that we can use to store 0 and 1 values.

For example, we can add sample data as shown below:

INSERT INTO TABLE_NAME(id, first_name, available) VALUES (1, 'Julius', 1);
INSERT INTO TABLE_NAME(id, first_name, available) VALUES (1, 'Anna', 0);

These inserts two records in the table where one indicates true and 0 indicates false.

Using Boolean in PostgreSQL

Both PostgreSQL and Google BigQuery database engines provide a native SQL boolean data type

The query below shows how to create a table with a boolean type column.

CREATE TABLE my_table(
id INT NOT NULL,
available BOOLEAN
);

You can then add Boolean data as shown:

INSERT INTO my_table(id, available) VALUES (1, TRUE);
INSERT INTO my_table(id, available) VALUES (2, FALSE);

NOTE: In BiQuery, the unknown type is replaced with NULL.

Using Boolean in MySQL

We can use the TINYINT data type for MySQL to store Boolean values. In MySQL, zero is considered false, and any non-zero value is true. Hence, we can use 0 and 1 to represent false and true.

You can use the BOOLEAN type in MySQL, simply a synonym for TINYINT. An example is shown below:

CREATE TABLE languages (
ID INT NOT NULL AUTO_INCREMENT,

Language_name VARCHAR(100),
Beginner_friendly BOOLEAN,
PRIMARY KEY (ID)
);

The above creates a table where the Beginner_friendly column stores boolean types. The Boolean type in MySQL is a synonym for the TINYINT type.

To explore more about MySQL Boolean type, check this tutorial.

https://linuxhint.com/mysql-boolean-data-type/

Using Boolean in Oracle Database

In Oracle, you can use a number type to represent a Boolean value. For example, you can use the number type with a value of 0 to represent false and 1 to mean true.

An example is as shown:

CREATE TABLE my_table(
id NOT NULL,
available NUMBER(1)
);

Setting the type as NUMBER(1) allows you to store 1 and 0 as true and false, respectively.

Conclusion

This article covered the Boolean data type in MySQL and how we can use it in various database engines.