Custom Search
 


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:

  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:

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

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.

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

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.

/*
<=> 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)

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.

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

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.

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





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


Copyright © 2014 GeeksEngine.com. All Rights Reserved.

This website is hosted by LunarPages.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy