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.
- 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.
id INT NOT NULL,
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, '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.
id INT NOT NULL,
You can then add Boolean data as shown:
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:
ID INT NOT NULL AUTO_INCREMENT,
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.
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:
id NOT NULL,
Setting the type as NUMBER(1) allows you to store 1 and 0 as true and false, respectively.
This article covered the Boolean data type in MySQL and how we can use it in various database engines.