MySQL Comparison Operators

MySQL comparison operators compare values and produce a Boolean-style result: true, false, or, for ordinary comparisons involving NULL, unknown. They are commonly used in a WHERE clause to decide which rows should be returned.

SELECT id,
       name,
       mark
FROM student
WHERE mark >= 85
ORDER BY mark DESC, id;

Comparison Operator Reference Top ↑

IN, BETWEEN, IS NULL, and LIKE are predicates rather than simple two-value operators, but they belong to the same practical family of SQL conditions used for filtering rows.

Equal To = Top ↑

Return students whose mark is exactly 85:

SELECT id,
       name,
       class,
       mark
FROM student
WHERE mark = 85
ORDER BY id;

For text values, quote the string:

SELECT id,
       name,
       class
FROM student
WHERE class = 'Six';

Not Equal To <> and != Top ↑

MySQL accepts both <> and != for ordinary not-equal comparison.

SELECT id,
       name,
       class
FROM student
WHERE class <> 'Six';

The equivalent MySQL form is:

SELECT id,
       name,
       class
FROM student
WHERE class != 'Six';

SELECT id,
       name,
       class
FROM student
WHERE class <> 'Six'
   OR class IS NULL;

Greater Than and Greater Than or Equal To Top ↑

Marks above 50:

SELECT id, name, mark
FROM student
WHERE mark > 50;

Marks of 85 or more:

SELECT id, name, mark
FROM student
WHERE mark >= 85;

The boundary value is excluded by > and included by >=.

Less Than and Less Than or Equal To Top ↑

Marks below 25:

SELECT id, name, mark
FROM student
WHERE mark < 25;

Marks of 25 or below:

SELECT id, name, mark
FROM student
WHERE mark <= 25;

NULL-safe Equality <=> Top ↑

MySQL's <=> operator compares two values like equality but handles NULL explicitly.

Consider this table:

Ordinary equality:

SELECT id,
       first_name,
       last_name
FROM table1
WHERE first_name = last_name;

returns the row where both strings are Ron, but not the row where both values are NULL.

Why? Because:

SELECT NULL = NULL AS ordinary_equality;

returns NULL, not true.

Now use NULL-safe equality:

SELECT id,
       first_name,
       last_name
FROM table1
WHERE first_name <=> last_name;

With <=>, two NULL values are considered equal for this comparison.

NULL-safe "not equal" Top ↑

MySQL does not have a separate NULL-safe not-equal symbol. Negate the NULL-safe equality test:

SELECT id,
       first_name,
       last_name
FROM table1
WHERE NOT (first_name <=> last_name);

See SQL NULL values.

IS NULL and IS NOT NULL Top ↑

To test whether one expression is NULL, use IS NULL:

SELECT id,
       first_name,
       last_name
FROM table1
WHERE first_name IS NULL;

To return non-NULL values:

SELECT id,
       first_name,
       last_name
FROM table1
WHERE first_name IS NOT NULL;

IN and NOT IN Top ↑

Use IN when a value may match any member of a set:

SELECT id,
       name,
       class
FROM student
WHERE class IN (
    'Four',
    'Five'
);

Use NOT IN to exclude members of the set:

SELECT id,
       name,
       class
FROM student
WHERE class NOT IN (
    'Four',
    'Five'
);

BETWEEN and NOT BETWEEN Top ↑

BETWEEN includes both boundary values:

SELECT id,
       name,
       mark
FROM student
WHERE mark BETWEEN 50 AND 60;

This is equivalent to:

WHERE mark >= 50
  AND mark <= 60

To return values outside that inclusive range:

SELECT id,
       name,
       mark
FROM student
WHERE mark NOT BETWEEN 50 AND 60;

IS TRUE, IS FALSE and IS UNKNOWN Top ↑

MySQL supports Boolean-style predicates such as IS TRUE, IS FALSE, and IS UNKNOWN.

Using the existing plus2_boolean sample table:

SELECT *
FROM plus2_boolean
WHERE mar IS TRUE;

Using IS NOT TRUE:

SELECT *
FROM plus2_boolean
WHERE jan IS NOT TRUE;

See MySQL IS operator.

LIKE Pattern Matching Top ↑

LIKE is used for pattern matching rather than ordinary equality:

SELECT id,
       name
FROM student
WHERE name LIKE 'John%';

% matches any sequence of characters. Use _ to match one character.

String Comparison and Collation Top ↑

Text comparisons depend on the active character set and collation. A case-insensitive collation can treat differently cased strings as equal.

SELECT name
FROM student
WHERE name = 'JOHN DEO';

Whether that matches John Deo depends on the column/expression collation.

STRCMP() Top ↑

STRCMP() compares two strings according to MySQL string-comparison rules and returns:

• 0 when the strings compare as equal,
• a negative value when the first string sorts before the second,
• a positive value when the first string sorts after the second,
• NULL if either argument is NULL.

SELECT STRCMP(
    'plus2net',
    'PLUS2NET'
);

Under a case-insensitive collation, these strings can compare as equal and return 0. The result is not universally guaranteed to be 0 under every possible collation.

Combining Comparisons with AND / OR Top ↑

Comparison expressions are often combined with logical operators:

SELECT id,
       name,
       class,
       mark
FROM student
WHERE class = 'Four'
  AND mark >= 60
ORDER BY mark DESC, id;

When both AND and OR appear, use parentheses to make the intended precedence obvious:

SELECT id,
       name,
       class,
       mark
FROM student
WHERE (
        class = 'Four'
        OR class = 'Five'
      )
  AND mark >= 60;

See SQL AND / OR logical operators.

PHP PDO Example Top ↑

When the comparison value comes from the application, bind it rather than concatenating it into SQL:

<?php
$minimum_mark=85;

$sql="SELECT id, name, class, mark
      FROM student
      WHERE mark >= :minimum_mark
      ORDER BY mark DESC, id";

$stmt=$dbo->prepare($sql);
$stmt->bindValue(
    ':minimum_mark',
    $minimum_mark,
    PDO::PARAM_INT
);
$stmt->execute();

foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
    echo '<p>'
        .htmlspecialchars(
            $row['name'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .' : '
        .(int)$row['mark']
        .'</p>';
}

Prepared statements protect data values. The comparison operator itself should come from trusted application logic rather than arbitrary user input.

Common Comparison Mistakes Top ↑

Comparing NULL with = or != Top ↑

Use IS NULL, IS NOT NULL, or <=> when NULL must be handled explicitly.

Assuming <> returns NULL rows Top ↑

column <> 'value' does not return NULL rows. Add OR column IS NULL if that is the required logic.

Forgetting BETWEEN is inclusive Top ↑

BETWEEN 50 AND 60 includes both 50 and 60.

Using NOT IN without checking for NULL Top ↑

NULL in a NOT IN subquery can make the result unknown. Filter NULL or use NOT EXISTS where appropriate.

Assuming string comparison case behavior Top ↑

Case and accent behavior depends on collation.

Treating STRCMP() as universally case-insensitive Top ↑

Its result follows MySQL string comparison/collation rules.

Allowing users to supply raw operators Top ↑

Bind values with prepared statements and select permitted comparison operators through trusted allowlisted application logic.

Sample SQL Data Top ↑

SQL dump of plus2_boolean table:

SQL dump of student3 table:

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: