SQL Comparison Operators

Summary: in this tutorial, you will learn about SQL comparison operators and how to use them to form conditions for filtering data.

The SQL comparison operators allow you to test if two expressions are the same. The following table illustrates the comparison operators in SQL:

Operator	Meaning
=	Equal
<>	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to

The result of a comparison operator has one of three values true, false, and unknown.

Equal to the operator(=)

The equal to operator compare the equality of two expressions:

expression1 = expression2

It returns true if the value of the left expression is equal to the value of the right expression; otherwise, it returns false.

For example, the following statement finds the employee whose last name is Himuro:

SELECT 
    employee_id, first_name, last_name
FROM
    employees
WHERE
    last_name = 'Himuro'; 

In this example, the query searches for the string Himuro in the last_name column of the employees table.

Note that the equal operator cannot be used to compare null values. For example, the intention of the following query is to find all employees who do not have phone numbers:

SELECT 
    employee_id, first_name, last_name, phone_number
FROM
    employees
WHERE
    phone_number = NULL;

However, it returns an empty result set because the following expression always returns false.

phone_number = NULL

To compare null values, you use the IS NULL operator instead:

SELECT 
    employee_id, first_name, last_name, phone_number
FROM
    employees
WHERE
    phone_number IS NULL;

Not equal to the operator (<>)

The not equal to (<>) operator compares two non-null expressions and returns true if the value of the left expression is not equal to the right one; otherwise, it returns false.

expression1 <> expression2

For example, the following statement returns all employees whose department id is not 8.

SELECT 
    employee_id, first_name, last_name, department_id
FROM
    employees
WHERE
    department_id <> 8
ORDER BY first_name , last_name;

You can use the AND operator to combine multiple expressions that use the not equal to (<>) operator. For example, the following statement finds all employees whose department id is not eight and ten.

SELECT 
    employee_id, first_name, last_name, department_id
FROM
    employees
WHERE
    department_id <> 8
        AND department_id <> 10
ORDER BY first_name , last_name; 

Greater than operator (>)

The greater than operator (>) compares two non-null expressions and returns true if the left operand is greater than the right operand; otherwise, the result is false.

expression1 > expression2

For example, to find the employees whose salary is greater than 10,000, you use the greater-than the operator in the WHERE clause as follows:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > 10000
ORDER BY salary DESC;

You can combine expressions that use various comparison operators using the AND or OR operator. For example, the following statement finds employees in department 8 and have a salary greater than 10,000:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > 10000 AND department_id = 8
ORDER BY salary DESC;

Less than operator (<)

The less than operator compares two non-null expressions. The result is true if the left operand evaluates to a value that is lower than the value of the right operand; otherwise, the result is false.

The following shows the syntax of the less-than operator:

expression1 < expression2

For example, the statement below returns all employees whose salaries are less than 10,000:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary < 10000
ORDER BY salary DESC;

Greater than or equal operator (>=)

The greater than or equal operator (>=) compares two non-null expressions. The result is true if the left expression evaluates to a value that is greater than the value of the right expression.

The following illustrates the syntax of the greater than or equal operator:

expression1 >= expression2

For example, the following statement finds employees whose salaries are greater than or equal to 9,000:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= 9000
ORDER BY salary;

Less than or equal to the operator(<=)

The less than or equal to operator compares two non-null expressions and returns true if the left expression has a value less than or equal to the value of the right expression; otherwise, it returns true.

The following shows the syntax of the less than or equal to the operator:

expression1 <= expression2

For example, the following statement finds employees whose salaries are less than or equal to 9,000:

In this tutorial, you have learned how to use the comparison operators to form comparison expressions for filtering data based on a specified condition.