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 |
Let's start by explaining the common comparison operators such as the equality operator.
Example - Equality Operator
In SQL, you can use the =
operator to test for equality in a query.
For example:
SELECT * FROM suppliers WHERE supplier_name = 'IBM';
In this example, the SELECT statement above would return all rows from the suppliers table where the supplier_name is equal to IBM.
Example - Inequality Operator
In SQL, there are two ways to test for inequality in a query. You can use either the <>
or !=
operator.
For example, we could test for inequality using the <>
operator, as follows:
SELECT * FROM suppliers WHERE supplier_name <> 'IBM';
In this example, the SELECT statement would return all rows from the suppliers table where the supplier_name is not equal to IBM.
Or you could also write this query using the !=
operator, as follows:
SELECT * FROM suppliers WHERE supplier_name != 'IBM';
Both of these queries would return the same results.
Example - Greater Than Operator
You can use the >
operator in SQL to test for an expression greater than.
SELECT * FROM customers WHERE customer_id > 499;
In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than 499. A customer_id equal to 499 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.
SELECT * FROM customers WHERE customer_id >= 499;
In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than or equal to 499. In this case, supplier_id equal to 499 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.
SELECT * FROM products WHERE product_id < 10;
In this example, the SELECT statement would return all rows from the products table where the product_id is less than 10. A product_id equal to 10 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.
SELECT * FROM products WHERE product_id <= 10;
In this example, the SELECT statement would return all rows from the products table where the product_id is less than or equal to 10. In this case, product_id equal to 10 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
EXISTSIN ( )