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

Consider the following facts when using comparison operators in a SELECT statement:

1. Comparison operators are used in WHERE clause.

2. Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL.

3. The comparison must be conducted between values of the same data type.

4. When comparing values of different data types, you can use CAST() function to convert a value to a specific type.

5. If CAST() function is not used when you compare strings with numbers, strings are automatically converted to numbers. The same is also true for numbers where numbers are automatically converted to strings as necessary. More about using CAST() function in Single-Row Functions section.

6. By default, string comparisons are not case sensitive and it relies on the current character set of the column, table, database, or server.

Part I 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: Using equal to or less than comparison.

/*
This query retrieves products where the number of units 
in stock is less than or equal to the reorder level. Also 
the number of units currently we have re-ordered 
is displayed as well.
*/
SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder 
FROM products
WHERE UnitsInStock <= ReorderLevel;

Query result set: 18 rows returned

Practice #2: Using not equal to comparison

/*
Retrieve all order details line items where quantity 
ordered is not equal to 120.
 
The two queries below produce the same result.
*/
SELECT OrderID, ProductID, Quantity
FROM order_details
WHERE Quantity <> 120;
 
SELECT OrderID, ProductID, Quantity
FROM order_details
WHERE Quantity != 120;

Query result set: 2147 rows returned

Practice #3: Using NULL-safe comparison

<=> is NULL-safe equal to. MySQL Documentation states that <=> operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

/*
<=> operator returns 1 if both operands 
are NULL, and returns 0 rather than NULL 
if one operand is NULL.
*/
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

Query result set:

Here is the query by using equal (=) operator. The result is different.

/*
= (equal to) operator returns NULL if 
either operand is NULL. 
*/
SELECT 1 = 1, NULL = NULL, 1 = NULL;

Practice #4: Using NULL-safe comparison to get the top employee (Vice President, Sales)

/*
NULL-safe comparison returns one row
because both operators are found to
contain NULLs.
 
This query returns the employee who
reports to no one.
*/
SELECT EmployeeID, FirstName, LastName 
FROM Employees
WHERE ReportsTo <=> NULL;

Query result set - 1 row returned

Here is the query by using equal (=) operator. The result is different.

/*
Normal equal to (=) comparison returns 
nothing (NULL result) because both 
operators are found to contain NULLs.
 
This query returns no result.
*/
SELECT EmployeeID, FirstName, LastName 
FROM Employees
WHERE ReportsTo = NULL;

Practice #5: Use ROW constructor for comparison in WHERE clause

ROW constructor is used to compare if two or more columns in a table are equal to a row of values.

The following query demonstrates the use of ROW constructor to compare a row of values. It's normally used in a query where a subquery returns a row of values.

/*
Query 1: 
This query finds out all employees whose EmployeeID 
is 8 and also live in USA.
*/
SELECT EmployeeID, FirstName, LastName
FROM employees
WHERE ROW(EmployeeID, Country) = (8, 'USA');
 
/*
Query 2: 
This query is equivalent to Query 1 but no
ROW constructor is used.
*/
SELECT EmployeeID, FirstName, LastName
FROM employees
WHERE (EmployeeID, Country) = (8, 'USA');
 
/*
Query 3: 
This query is equivalent to both Query 1 and Query 2.
*/
SELECT EmployeeID, FirstName, LastName
FROM employees
WHERE EmployeeID = 8 and Country = 'USA';

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 II

9. Using LIKE Comparison Operator

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page