Basic about Comparison Operators – using and example

This SQL tutorial explores all of the assessment operators used in SQL to test for equality and inequality, as properly as the greater advanced operators.

Description

Comparison operators are used in the WHERE clause to determine which data to select. Here is a listing of the contrast 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 price 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 p.c and _ EXISTS Condition is met if subquery returns at least one row

DDL/DML for Examples

If you favor to follow alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then attempt the examples in your very 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 desk called suppliers with the following data:

supplier_id supplier_name town 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:

Try It

SELECT *
FROM suppliers
WHERE supplier_name = 'Microsoft';

There will be 1 document selected. These are the outcomes that you ought to see:

supplier_id supplier_name town state 100 Microsoft Redmond Washington

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

Example – Inequality Operator

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

Let’s use the same suppliers desk as the previous example.

supplier_id supplier_name town 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 check for inequality the usage of the <> operator:

Try It

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

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

Try It

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

There will be 8 information selected. These are the outcomes you have to see with both 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 the place the supplier_name is no longer equal to Microsoft.

Example – Greater Than Operator

You can use the > operator in SQL to check for an expression larger than.

In this example, we have a desk referred to as clients 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:

Try It

SELECT *
FROM customers
WHERE customer_id > 6000;

There will be three documents selected. These are the results that you have to see:

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

In this example, the SELECT announcement would return all rows from the customers desk the place the customer_id is higher than 6000. A customer_id equal to 6000 would no longer be covered in the end result set.

Example – Greater Than or Equal Operator

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

Let’s use the identical clients desk 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:

Try It

SELECT *
FROM customers
WHERE customer_id >= 6000;

There will be four data selected. These are the consequences that you need to 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 declaration would return all rows from the customers desk the place the customer_id is greater than or equal to 6000. In this case, the supplier_id equal to 6000 would be protected in the result set.

Example – Less Than Operator

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

In this example, we have a table referred to as merchandise 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:

Try It

SELECT *
FROM products
WHERE product_id < 5;

There will be 4 files selected. These are the consequences 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 merchandise table the place the product_id is less than 5. A product_id equal to 5 would no longer be blanketed in the end result set.

Example – Less Than or Equal Operator

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

Let’s use the identical 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:

Try It

SELECT *
FROM products
WHERE product_id <= 5;

There will be 5 files selected. These are the effects that you should see:

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

In this example, the SELECT announcement would return all rows from the products desk the place the product_id is less than or equal to 5. In this case, the product_id equal to 5 would be protected in the result set.

Example – Advanced Operators

To examine more about the advanced evaluation operators, we’ve got written tutorials to talk about every one individually. These subjects will be included later, or you can leap to one of these tutorials now.

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