EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Boolean
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL Boolean

By Payal UdhaniPayal Udhani

MySQL Boolean

Introduction to MySQL Boolean

There is none of the built-in datatype present in MySQL for boolean values. However, MySQL provides us with TINYINT data type which can store values of integers with small values. We can declare the data type of the column whose behavior is like boolean with TINYINT(1) datatype. That will function in the same way as boolean. The 0(zero) is considered as the FALSE value while all other non-zero values are considered as 1 in MySQL. There are keywords present in MySQL like BOOLEAN or BOOL that are internally treated in the same manner as TINYINT(1). In this article, we will learn about the available data types in MySQL that can be used to store boolean values and further learn how we can use boolean values in tables with the help of examples.

How to Declare & Store Boolean Values in MySQL?

We can specify the datatype of the column that might store the boolean value as BOOLEAN, BOOL, or TINYINT(1). All of these behave in the same manner and are synonyms of each other. The FALSE value is evaluated as 0 and stored as 0 in the column declared with the data type of any of the above three mentioned databases. All other values such as TRUE or any other non-zero value will be considered and stored as 1 in that column for that record. Let us fire one simple command in MySQL.

Command:

SELECT TRUE, FALSE;

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Output:

the output of the above query statement is as follows

MySQL boolean Example 1

Hence, we can conclude that MySQL considers true as 1 and false as 0. Note that true and false are treated the same irrespective of the case in which they are used.

Example to Implement of MySQL Boolean

1. Let us create one table named marathon_players that will store the details of the persons participated in the marathon and have columns that will store boolean values in it such as healthChecked and runCompleted. We will declare the datatype of healthChecked column as BOOLEAN and runCompleted as BOOL and check the results of the created table. For the table creation, we will make the use of the following CREATE TABLE query.

Query:

CREATE TABLE marathon_players(
player_id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(100),
Age INT,
healthChecked VARCHAR(100),
runCompleted VARCHAR(100),
completionTime TIME,
PRIMARY KEY (player_id)
);

Now, let us simply describe the created table by using the following query statement:

DESC marathon_players;

Output:

Executing the above query gives the following output

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,584 ratings)

Student Details Example 2

2. We can conclude that even when we specified the datatype of healthChecked column as BOOLEAN and runCompleted as BOOL they got converted and internally treated as TINYINT(1) datatype automatically by Mysql.

Let us now insert some values in the marathon_players table using the following insert queries:

Query:

INSERT INTO marathon_players (player_id, Name, Age, healthChecked, runCompleted, completionTime) VALUES('1','Ramesh','25','true','false','02:50:56');
INSERT INTO marathon_players (player_id, Name, Age, healthChecked, runCompleted, completionTime) VALUES('2','Suresh','27','-12','25','01:30:21');

3. Now, let us fire the following select query to see what results are retrieved –

Query:

SELECT * FROM marathon_players;

Output:

MySQL boolean Example 3

4. We can see that the first record that we inserted with Ramesh name, the healthChecked, and runCompleted columns were specified as true and false while insertion which is stored as 1 and 0 respectively. While in the second record that we inserted -12 and 25 values in healthChecked and runCompleted columns were stored in the same format that we declared and with same values even when we declared those columns as BOOLEAN and BOOL as they were considered as TINYINT datatypes internally and expanded its column length to store the value.

5. IS TRUE, IS FALSE, IS NOT TRUE, IS NOT FALSE Clauses.

To know whether a particular column contains the value that evaluates to true or false, MySQL provides us with four clauses namely IS TRUE, IS FALSE, IS NOT TRUE, IS NOT FALSE clauses. Out of them, IS TRUE and IS NOT FALSE behave in the same fashion and IS NOT TRUE and similarly IS FALSE function and result in the same results. Let us try to find out the records in our table marathon_players whose healthChecked columns value is true or is equivalent to true. For this, I will first use the clause IS TRUE and my query statement will be as follows –

Code:

SELECT * FROM marathon_players WHERE healthChecked IS TRUE;

Output:

MySQL boolean Example 4

6. We can see from the results that 1 and 12 values both are valuated to true. Any non-zero value whether it be positive or negative will be considered as true itself. Now, let us use IS NOT FALSE clause and see whether we retrieve the same results. Our query statement will be as follows –

Query:

SELECT * FROM marathon_players WHERE healthChecked IS NOT FALSE;

Output:

MySQL boolean Example 5

Hence, we can conclude that both clauses work in the same manner.

7. Now, we will check the working of the remaining two clauses by checking the results for all those persons whose run was not completed by checking the value of the runCompleted column. Let us prepare the query that will make the use of the IS FALSE clause in it. The query will be as follows:

Query:

SELECT * FROM marathon_players WHERE runCompleted IS FALSE;

Output:

IS TRUE clause Example 6

8. Now, let us use the IS NOT TRUE clause in our query and retrieve the results. Our query statement is as follows –

Query:

SELECT * FROM marathon_players WHERE runCompleted IS NOT TRUE;

Output:

We can conclude that IS NOT TRUE and IS FALSE give the same output.

IS NOT TRUE clause Example 8

Conclusion

MySQL does not provide any specific datatype that will store the boolean values. However, BOOLEAN and BOOL are the keywords that can be used to declare the data type of the column that is internally treated and considered as TINYINT(1) datatype. Hence, we can say that BOOLEAN and BOOL are synonyms of TINYINT(1) datatype. Any true irrespective of the case in which it is specified is considered and stored as 1. Any non-zero value and 1 are treated as TRUE in MYSQL when any of the IS TRUE, IS FALSE, IS NOT TRUE, IS NOT FALSE clauses are used. The opposite is applicable for 0 and FALSE in MySQL.

Recommended Articles

This is a guide to MySQL Boolean. Here we discuss the Introduction to MySQL Boolean and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

  1. MySQL Operators
  2. Jira vs Github
  3. MySQL String functions
  4. MySQL vs SQLite
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more