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

Using Comparison Operators, Part I

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.

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

Comparison operators are used in WHERE clause.
Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL.
The comparison must be conducted between values of the same data type.
When comparing values of different data types, you can use CAST() function to convert a value to a specific type.
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.
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:

Operator	Meaning
=	Equal to
<=>	NULL-safe equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to
!=	Not equal to

Practice #1: Using equal to or less than comparison.

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 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
Equal to or greater than comparison operator

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
Using not equal to comparison operator

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:
Result of using <=> NULL-safe operator

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;

Query result set:
Result of using equal (=) operator

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
Using NULL-safe operator

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;

Query result set - 0 rows returned
Result of using equal (=) operator

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:
Use arithmetic operation in WHERE clause

Next tutorial: Using Comparison Operators, Part II
Back to Section Index: Single Table SELECT Statement
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search