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

Part II of this tutorial covers the following comparison operators:

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

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

/*
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:

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:

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:

Other tutorials in this category

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Using Literal Character Strings

4. Perform Arithmetic Operations

5. Use WHERE Clause to Conditionally Select Rows

6. Sorting Data

7. Using LIMIT Clause to Constrain the Number of Rows Retrieved

8. Using Comparison Operators, Part I

9. Using LIKE Comparison Operator

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page