comparison Operators in MySQL with sample query

Comparison operators in SQL are used to compare two values and return a Boolean value (TRUE or FALSE). They are used in WHERE clauses to filter rows from a table based on the results of the comparison.

Operators	DESCRIPTION
=	Equal to , returns 1 when both operands are same
!=	Not Equal to , returns 1 when both operands are different
<>	Not Equal to , returns 1 when both operands are different
<=>	Null safe equal to, returns 1 when both operands are same including NULL value
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
IN	Matching from a set of values
NOT IN	NOT Matching from a set of values
IS	Check against Boolean values
IS NULL	Checking NULL value
BETWEEN	Between a range of values
STRCMP	String Comparison
LIKE	Pattern matching

Here are some sample queries using comparison operators of MySQL

SELECT * FROM student where mark=85

id	name	class	mark	sex
2	Max Ruin	Three	85	male
8	Asruid	Five	85	male

SELECT query
Using not equal to

SELECT * FROM student WHERE class <> 'Six'

Using Less than

SELECT * FROM student WHERE mark < 25

Using Less than or equal to

SELECT * FROM student WHERE mark <= 25

Using greater than

SELECT * FROM student WHERE mark > 50

Using greater than equal to

SELECT * FROM student WHERE mark >= 25

Using IN

SELECT * FROM `student` WHERE class IN ('Four','Five')

SQL IN query
Using NOT IN

SELECT * FROM `student` WHERE class NOT IN ('Four','Five')

Using IS

Check data against Boolean value, download sql dump of plus2_boolean table at the end of this page.

SELECT * FROM `plus2_boolean` WHERE mar IS TRUE

using IS NOT

SELECT * FROM `plus2_boolean` WHERE jan  IS NOT TRUE

SQL IS Operator

Using BETWEEN

SELECT * FROM student WHERE mark BETWEEN 50 AND 60

Using NOT BETWEEN

SELECT * FROM student WHERE mark NOT BETWEEN 50 AND 60

SQL BETWEEN query

NULL safe operators <=>

We can't compare NULL value with NULL value as the meaning of NULL value is absence of any value. Check this table ( table1)

id	first_name	last_name
2	Alex	John
3	Ron	Ron
4	NULL	NULL

SELECT * FROM table1 WHERE first_name=last_name

The output is here

id	first_name	last_name
3	Ron	Ron

Why the record with NULL value as first_name and last_name is not included in the output ?
This is because we can't compare NULL value with NULL value . Read this output

SELECT NULL=NULL

Output of above query is NULL. ( Why ? )
Let us try one more query

SELECT * FROM table1 WHERE first_name <=> last_name

The output is here

id	first_name	last_name
3	Ron	Ron
4	NULL	NULL

By using Null safe operator we can get two records including the row having NULL value in both the columns.
SQL with NULL Value

Using STRCMP

We can compare strings by using string function STRCMP

SELECT STRCMP('plus2net','PLUS2NET')

As both strings are same, so we will get 0 as output.
STRCMP: String comparison
SQL dump of plus2_boolean table

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);

LOGICAL Operators SELECT query

Subscribe to our YouTube Channel here