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:
- Introduction – MySQL WHERE clause
- Basic comparison operators
- IN operator
- BETWEEN operator
- LIKE operator
- IS NULL operator
- AND & OR operators
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)
MySQL Comparison Operators Limit
In MySQL basic Comparison operators are limited to a single value. For example, if you would like to know the names of the Books whose price equals 5000, 6000, 8500, or 10,000, you cannot use the following MySQL WHERE clause: