Comparison Operators

 

SQL: Comparison Operators

This SQL tutorial explores all of the comparison operators used in SQL to test for equality and inequality, as well as the more advanced operators.

 

Description

Comparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison operators that you can use in SQL:

Comparison Operator	Description
=	Equal
<>	Not Equal
!=	Not Equal
>	Greater Than
>=	Greater Than or Equal
<	Less Than
<=	Less Than or Equal
IN ( )	Matches a value in a list
NOT	Negates a condition
BETWEEN	Within a range (inclusive)
IS NULL	NULL value
IS NOT NULL	Non-NULL value
LIKE	Pattern matching with % and _
EXISTS	Condition is met if subquery returns at least one row

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Equality Operator

In SQL, you can use the = operator to test for equality in a query.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name = 'Microsoft';

There will be 1 record selected. These are the results that you should see:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington

In this example, the SELECT statement above would return all rows from the suppliers table where the supplier_name is equal to Microsoft.

Example - Inequality Operator

In SQL, there are two ways to test for inequality in a query. You can use either the <> or != operator. Both will return the same results.

Let's use the same suppliers table as the previous example.

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Enter the following SQL statement to test for inequality using the <> operator:

SELECT *
FROM suppliers
WHERE supplier_name <> 'Microsoft';

OR enter this next SQL statement to use the != operator:

SELECT *
FROM suppliers
WHERE supplier_name != 'Microsoft';

There will be 8 records selected. These are the results you should see with either one of the SQL statements:

supplier_id	supplier_name	city	state
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

In the example, both SELECT statements would return all rows from the suppliers table where the supplier_name is not equal to Microsoft.

Example - Greater Than Operator

You can use the > operator in SQL to test for an expression greater than.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id > 6000;

There will be 3 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
7000	Reynolds	Allen	google.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	oracle.com

In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than 6000. A customer_id equal to 6000 would not be included in the result set.

Example - Greater Than or Equal Operator

In SQL, you can use the >= operator to test for an expression greater than or equal to.

Let's use the same customers table as the previous example.

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id >= 6000;

There will be 4 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than or equal to 6000. In this case, the supplier_id equal to 6000 would be included in the result set.

Example - Less Than Operator

You can use the < operator in SQL to test for an expression less than.

In this example, we have a table called products with the following data:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE product_id < 5;

There will be 4 records selected. These are the results that you should see:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50

In this example, the SELECT statement would return all rows from the products table where the product_id is less than 5. A product_id equal to 5 would not be included in the result set.

Example - Less Than or Equal Operator

In SQL, you can use the <= operator to test for an expression less than or equal to.

Let's use the same products table as the previous example.

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE product_id <= 5;

There will be 5 records selected. These are the results that you should see:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75

In this example, the SELECT statement would return all rows from the products table where the product_id is less than or equal to 5. In this case, the product_id equal to 5 would be included in the result set.

Example - Advanced Operators

To learn more about the advanced comparison operators, we've written tutorials to discuss each one individually. These topics will be covered later, or you can jump to one of these tutorials now.

• IN ( )
• NOT
• BETWEEN
• IS NULL
• IS NOT NULL
• LIKE
• EXISTS