Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

BOOLEAN

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Boolean Datatype
A Boolean data type is used to represent truth values of logic and Boolean algebra. It has two possible values: either true or false.
For example, if a customer wants to see all the bikes that are black in colour, we can filter them using BOOLEAN operator, as given in the following table −
﻿
Here, 'IS_BLACK' is the BOOLEAN column that returns either true or false values based on the colours of the bikes.
Boolean in MySQL
In MySQL, there is no built-in Boolean or Bool data type. Instead MySQL provides us with the TINYINT datatype to store the Boolean values.
MySQL considers the value 0 as FALSE and 1 as TRUE. We can also store NULL values using the TINYINT datatype.
The Boolean values (such as TRUE and FALSE) are not case-sensitive.
Syntax
Following is the syntax of the BOOLEAN operator in MySQL −
CREATE TABLE table_name (
   Column_name BOOLEAN
);
﻿
Example
In MySQL, 0 is defined as FALSE and any non-zero values are defined as TRUE −
SELECT TRUE, FALSE;
﻿
Output
As we can see in the output below, TRUE and FALSE are represented as 1 and 0 −
TRUE
FALSE
1
0
Example
In MySQL, the Boolean values (TRUE and FALSE) are case-insensitive −
SELECT true, false, TRUE, FALSE, True, False;
﻿
Output
The output produced is as given below −
true
false
TRUE
FALSE
True
False
1
0
1
0
1
0
Example
Now, let's create a table with the name CUSTOMERS using the following query. Here, the AVAILABILITY column specifies whether the customer is available or not. If the bit value is 0 (FALSE), the customer is not available. If it is 1(TRUE), the customer is available −
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   NAME VARCHAR(40),
   AVAILABILITY BOOLEAN
);
﻿
Following is the output produced −
Query OK, 0 rows affected (0.02 sec)
﻿
To get the information about the CUSTOMERS table, use the following query −
DESCRIBE CUSTOMERS;
﻿
If we look at the AVAILABILITY column, which has been set to BOOLEAN while creating the table, it now shows type of TINYINT −
Field
Type
Null
Key
Default
Extra
ID
int
NO
PRI
NULL
auto_increment
NAME
varchar(40)
YES
﻿
NULL
﻿
AVAILABILITY
tinyint(1)
YES
﻿
NULL
﻿
Now, let us insert some records into the CUSTOMERS table using the following INSERT query −
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', FALSE),
(2, 'Khilan', TRUE),
(4, 'Kaushik', NULL);
﻿
Using the below query, we can display all the values in the table −
SELECT * FROM CUSTOMERS;
﻿
We can see the values in the AVAILABILITY column are set to 0 and 1 respectively. −
ID
NAME
AVAILABILITY
1
Ramesh
0
2
Khilan
1
4
Kaushik
NULL
Replacing BOOLEAN 0,1 with TRUE and FALSE
As we can see in the above CUSTOMERS table, the BOOLEAN data type shows 0 and 1 values instead of TRUE and FALSE. In MySQL, we can convert BOOLEAN data type to TRUE and FALSE values using the CASE statement.
The MySQL CASE statement is a conditional statement that goes through conditions and return a values when the first condition is met. Therefore, once a condition is true, it will stop reading the next piece of code and return the result.
If no conditions are true, it will return the value in the ELSE clause. If no ELSE clause is present and no conditions are true, it returns NULL.
Syntax
Following is the syntax of CASE statement in MySQL −
CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ...
   WHEN conditionN THEN resultN
   ELSE result
END;
﻿
Example
To get a better understanding, let us consider the BIKES table created using the query below −
CREATE TABLE BIKES (
   S_NO INT AUTO_INCREMENT PRIMARY KEY,
   COMPANY VARCHAR(40) NOT NULL,
   PRICE INT NOT NULL,
   COLOUR VARCHAR(40) NOT NULL,
   IS_BLACK BOOLEAN
);
﻿
Example
Output of the above code is as follows −
Query OK, 0 rows affected (0.03 sec)
﻿
Now, let us insert values into the BIKES table using the INSERT statement as shown below −
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) 
VALUES ('Royal Enfield', 300000, 'Black', 1);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) 
VALUES ('BMW', 900000, 'Blue', 0);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) 
VALUES ('Jawa', 150000, 'Black', 1);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK) 
VALUES ('Triumph', 1200000, 'Red', 0);
﻿
The BIKES table obtained is as follows −
S_NO
COMPANY
PRICE
COLOUR
IS_BLACK
1
Royal Enfield
300000
Black
1
2
BMW
900000
Blue
0
3
Jawa
150000
Black
1
4
Triumph
1200000
Red
0
Now, let us display all the records from the BIKES table, where the colour BLACK is represented by either TRUE or FALSE −
SELECT *,
CASE IS_BLACK
WHEN 1 THEN 'TRUE'
WHEN 0 THEN 'FALSE'
END AS IS_BOOLEAN
FROM BIKES;
﻿
Output
The output is displayed as follows −
S_NO
COMPANY
PRICE
COLOUR
IS_BLACK
IS_BOOLEAN
1
Royal Enfield
300000
Black
1
TRUE
2
BMW
900000
Blue
0
FALSE
3
Jawa
150000
Black
1
TRUE
4
Triumph
1200000
Red
0
FALSE
Example
In the following query, we are filtering the records from the BIKES table where colour black is TRUE −
SELECT * FROM BIKES WHERE IS_BLACK IS TRUE;
﻿
Output
As we can see the output below, Royal Enfield and Jawa are black in color (true) −
S_NO
COMPANY
PRICE
COLOUR
IS_BLACK
1
Royal Enfield
300000
Black
1
3
Jawa
150000
Black
1
Example
In here, we are filtering the records from the BIKES table where color black is FALSE −
SELECT * FROM BIKES WHERE IS_BLACK IS FALSE;
﻿
Output
The output says that BMW and Triumph are not black in colour(false) −
S_NO
COMPANY
PRICE
COLOUR
IS_BLACK
2
BMW
900000
Blue
0
4
Triumph
1200000
Red
0
Boolean Operator Using a Client Program
In addition to perform the Boolean Operator in MySQL table using MySQL query, we can also perform the another operation on a table using a client program.
MySQL provides various Connectors and APIs using which you can write programs (in the respective programming languages) to communicate with the MySQL database. The connectors provided are in programming languages such as, Java, PHP, Python, JavaScript, C++ etc. This section provides programs to execute Boolean Operator in MySQL Table.
Syntax
Following are the syntaxes of the MySQL Boolean Operator in various programming languages −
PHPNodeJSJavaPython
The MySQL PHP connector mysqli provides a function named query() to execute an SQL query in the MySQL database. Depending on the type of query, it retrieves data or performs modifications within the database.
This function accepts two parameters namely −
$sql: This is a string value representing the query.
$resultmode: This is an optional parameter which is used to specify the desired format of the result. Which can be MYSQLI_STORE_RESULT (buffered result set object) or, MYSQLI_USE_RESULT (unbuffered result set object) or, MYSQLI_ASYNC.
To perform the Boolean operation in MySQL table, we need to execute the CREATE TABLE statement using this function as −
$sql = "CREATE TABLE table_name ( Column_name BOOLEAN )";
$mysqli->query($sql);
﻿
Example
Following are the implementations of this operation in various programming languages −
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
//create table with boolean column
$sql = 'CREATE TABLE CUSTOMERS ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(40), AVAILABILITY BOOLEAN);';
$result = $mysqli->query($sql);
if($result){
   printf("Table created successfully...!\n");
}
//insert data into created table
$q = "INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (1, 'Ramesh', TRUE)";
If($res = $mysqli->query($q)){
   printf("Data inserted successfully...!\n");
}
//now display the table records
$s = "SELECT * FROM CUSTOMERS";
If($r = $mysqli->query($s)){
   printf("Select query executed successfully...!\n");
   printf("Table records: \n");
   while($row = $r->fetch_assoc()) {
       printf("Id %d, Name: %s, AVAILABILITY: %s",   
       $row["ID"],
       $row["NAME"],
       $row["AVAILABILITY"]);
       printf("\n");         
    }
}
else {
   printf('Failed');
}
$mysqli->close();
﻿
Output
The output obtained is as follows −
Table created successfully...!
Data inserted successfully...!
Select query executed successfully...!
Table records:
Id 1, Name: Ramesh, AVAILABILITY: 1       
﻿
﻿
﻿
﻿
﻿
﻿
﻿


﻿

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(40)	YES		NULL
AVAILABILITY	tinyint(1)	YES		NULL

S_NO	COMPANY	PRICE	COLOUR	IS_BLACK
1	Royal Enfield	300000	Black	1
2	BMW	900000	Blue	0
3	Jawa	150000	Black	1
4	Triumph	1200000	Red	0

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Boolean Datatype

Boolean in MySQL

Syntax

Example

Output

Example

Output

Example

Replacing BOOLEAN 0,1 with TRUE and FALSE

Syntax

Example

Example

Output

Example

Output

Example

Output

Boolean Operator Using a Client Program

Syntax

Example

Output