MySQL IS operator to check boolean & UNKNOWN values

IS operator checks data against Boolean values. Boolean data can take values TRUE or FALSE or UNKNOWN.

SELECT 1 IS  TRUE, 0 IS FALSE , NULL IS UNKNOWN

Output

1 IS TRUE	0 IS FALSE	NULL is UNKNOWN
1	1	1

Reading data from tinyint field

You can download the plus2_boolean table from here.

SELECT * FROM `plus2_boolean` WHERE mar IS TRUE

If data is equal to 1 or more than it will return TRUE.

name	jan	feb	mar
Alex	1	0	1
John	1	0	2
Lone	1	1	4
King	NULL	NULL	1

Using IS NOT TRUE

SELECT * FROM `plus2_boolean` WHERE jan  IS NOT TRUE

This includes NULL data also.

name	jan	feb	mar
Ronn	0	1	0
Raju	NULL	1	0
King	NULL	NULL	1

Boolean operator IS can be used to check UNKNOWN values. This will return True for Null values.

SELECT * FROM `plus2_boolean` WHERE jan  IS UNKNOWN

name	jan	feb	mar
Raju	NULL	1	0
King	NULL	NULL	1

Watch the difference between IS NOT TRUE and IS UNKNOWN, both are not same.
More on NULL Value & Null safe operator

Tutorial on how checkbox is used to update Boolean data in MySQL table.

Using IS operator in varchar columns

We have string data in our student3 table ( download the sql dump ).

SELECT * from student3 where class IS TRUE

Output

id	name	class	mark
7	My John Rob	5	5

Any value 1 or more will return True while comparing with IS operator. Here for record no 7 we have 5 in class column, so it returns True.

SELECT * from student3 WHERE class IS FALSE

We will get all records except the records where class value is 1 or more ( one record , id = 7 ) , where NULL value is present. ( three records id 2,4 ,6 )

SELECT * from student3 WHERE class IS UNKNOWN

Output

id	name	class	mark
2	Max Ruin	NULL	85
4	Krish Star	NULL	NULL
6	Alex John	NULL	55

SQL dump of student3 table

CREATE TABLE IF NOT EXISTS `student3` (
  `id` int(2) NOT NULL DEFAULT '0',
  `name` varchar(50) NOT NULL DEFAULT '',
  `class` varchar(10) DEFAULT '',
  `mark` int(3) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student3`
--

INSERT INTO `student3` (`id`, `name`, `class`, `mark`) VALUES
(1, 'John Deo', 'Four', NULL),
(2, 'Max Ruin', NULL, 85),
(3, 'Arnold', 'Three', NULL),
(4, 'Krish Star', NULL, NULL),
(5, 'John Mike', 'Four', NULL),
(6, 'Alex John', NULL, 55),
(7, 'My John Rob', '5', 5),
(8, 'Asruid', 'Five', 85),
(9, 'Tes Qry', 'Six', 78),
(10, 'Big John', 'Four', 55),
(11, 'Ronald', 'Six', 89),
(12, 'Recky', 'Six', 94),
(13, 'Kty', 'Seven', 88),
(14, 'Bigy', 'Seven', 88),
(15, 'Tade Row', 'Four', 88),
(16, 'Gimmy', 'Four', 88),
(17, 'Tumyu', 'Six', 54),
(18, 'Honny', 'Five', 75),
(19, 'Tinny', 'Nine', 18),
(20, 'Jackly', 'Nine', 65),
(21, 'Babby John', 'Four', 69),
(22, 'Reggid', 'Seven', 55),
(23, 'Herod', 'Eight', 79),
(24, 'Tiddy Now', 'Seven', 78),
(25, 'Giff Tow', 'Seven', 88),
(26, 'Crelea', 'Seven', 79),
(27, 'Big Nose', 'Three', 81),
(28, 'Rojj Base', 'Seven', 86),
(29, 'Tess Played', 'Seven', 55),
(30, 'Reppy Red', 'Six', 79),
(31, 'Marry Toeey', 'Four', 88),
(32, 'Binn Rott', 'Seven', 90),
(33, 'Kenn Rein', 'Six', 96),
(34, 'Gain Toe', 'Seven', 69),
(35, 'Rows Noump', 'Six', 88);

Comparison Operators SQL CASE SQL sum in multiple columns

Subscribe to our YouTube Channel here