Comparision Operators

OperatorsDESCRIPTION
=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 NULLChecking 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
idnameclassmarksex
2Max RuinThree85male
8AsruidFive85male
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)
idfirst_namelast_name
2AlexJohn
3RonRon
4NULLNULL
SELECT * FROM table1 WHERE first_name=last_name
The output is here
idfirst_namelast_name
3RonRon
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
idfirst_namelast_name
3RonRon
4NULLNULL
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 functino STRCMP
SELECT STRCMP('plus2net','PLUS2NET')
As both strings are same, so we will get 0 as output.
STRCMP: String Comparision
SQL dump of plus2_boolean table

SQL dump of student3 table
LOGICAL Operators SELECT query


plus2net.com






Post your comments , suggestion , error , requirements etc here
















We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer