How to Use Comparison Operators in SQL SELECT Statement for MySQL, Part II

Using Comparison Operators, Part II

Comparison operators are used in WHERE clause that compare one expression to another.

Read Prerequisites for this tutorial and practices if you haven't done so.

Part II of this tutorial covers the following comparison operators:

Operator	Meaning
BETWEEN...AND...	Check whether a value is between two values (inclusive)
IN (value1, value2,...)	Check whether a value is within a set of values
LIKE	Character pattern matching
IS	Test a value against a boolean which can be TRUE, FALSE, UNKNOWN
IS NULL	Test if a value is NULL

Practice #1: Use BETWEEN operator to display rows based on a range of values.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query returns rows from products table
for any product where the number of units 
ordered is between 15 and 40, inclusive.
*/
SELECT ProductID, ProductName, UnitsOnOrder
FROM products
WHERE UnitsOnOrder BETWEEN 15 AND 40;

Query result set - 5 rows returned:
Use BETWEEN operator

Practice #2: Use IN operator

/*
This query displays employees whose 
manager's EmployeeID is 2 or 5.
*/
SELECT EmployeeID, FirstName, LastName, ReportsTo
FROM employees
WHERE ReportsTo IN (2, 5)
ORDER BY ReportsTo;

Query result set - 8 rows returned:
Use IN operator

Practice #3: Use LIKE operator

See Using LIKE Operator section for detailed information.

Practice #4: Use IS NULL operator

-- Find who is the top employee in the reporting structure.
-- That is, the employee who reports to no one.
SELECT EmployeeID, FirstName, LastName
FROM employees
WHERE ReportsTo IS NULL;

IS NULL operator tests if a value is NULL. A NULL value does not mean zero or empty string. Zero is an integer data type. Empty string is a zero length string data type. NULL value means the value is undefined, unknown, or unassigned. Therefore, we can't use equal sign (=) to check if a value is equal to or not equal to NULL. We can use IS NULL operator or use NULL-safe operator.

The query above displays the employee who does not report to anyone.

Query result set - 1 row returned:
The employee who reports to no one

Next tutorial: Using LIKE Comparison Operator
Back to Section Index: Single Table SELECT Statement
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search