MySQL Comparison Operators

This SQL tutorial focuses on the basic Comparison Operators in MySQL, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the SQL WHERE clause in MySQL. To read additional posts regarding this subject, please use the following links:

MySQL Comparison Operators

Comparison conditions in MySQL are used to compare one expression to another value or expression. This section explains how to use the basic Comparison Conditions (as mentioned earlier, = , < , > , => , =<, <>) .

Comparing Numbers

In this MySQL example, the explicit value of 50 is compared to the price value in the BookPrice column.

SELECT BookID, BookName, BookPrice
FROM Books
WHERE  BookPrice  =  50

In this MySQL example, the explicit value of 597 is compared to the author number in the AuthorID column.

SELECT BookID, BookName, BookPrice, AuthorID
FROM Books
WHERE  AuthorID  =  60

In this MySQL example, the explicit value of 27 is compared to the age value in the Age column.

SELECT student_id, last_name, first_name, age
FROM students
WHERE  age  =  27

It is not possible to write the numeric value together with special characters. also, numeric values should not be enclosed within single quotes.

SELECT BookID, BookName, BookPrice
FROM Books
WHERE  BookPrice =    $50
-- (error)

SELECT BookID, BookName, BookPrice, discount_percent
FROM Books 
WHERE discount_percent = %20 
-- (error) 
-- * Usually these type of numbers are expressed as a number with a decimal point
-- * For example 0.2

SELECT BookID, BookName, BookPrice
FROM Books
WHERE  BookPrice  =   50,000
-- (error)

SELECT BookID, BookName, BookPrice
FROM Books
WHERE BookPrice   =   50
-- (valid comparison)

If the requested comparison does not match table values (for example, the highest price in the table is 25 and you would like to display the books whose price is greater than 20,000), no results will be retrieved and no error will be generated either.

Comparing Strings

In MySQL the string value must be enclosed within single quotes (‘string value’).

SELECT BookID, BookName
FROM Books
WHERE  BookName = Dune
-- (error)

SELECT BookID, BookName  
FROM Books
WHERE  BookName = 'Dune'
-- (valid comparison)

SELECT BookID , BookName, Publisher
FROM books
WHERE Publisher = 'Carroll & Graf Publishers'
-- (valid comparison)

In MySQL the string values are not case sensitive by default. The following MySQL queries will produce the same result:

SELECT BookName
FROM Books
WHERE BookName = 'DUNE' 

SELECT BookName
FROM Books
WHERE BookName = 'Dune'

SELECT BookName
FROM Books
WHERE BookName = 'dune'

Comparing Dates

In MySQL, comparing with dates requires specifying the date value within single quotes (‘date value’). It also requires using the appropriate MySQL date format, as date values are format sensitive.

SELECT customer_id, customer_name, birthdate
FROM customers
WHERE  birthdate  = 17-MAY-1980
-- (0 rows returned, single quotes are missing)

SELECT customer_id, customer_name, birthdate
FROM customers
WHERE birthdate = '17-MAY-1980'
-- (0 rows returned, not a valid date format)    

SELECT customer_id, customer_name, birthdate
FROM customers
WHERE birthdate = '1980-08-17'
-- (valid comparison)