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:

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

Equal to operator(=)

The equal to operator compares the equality of two expressions:

expression1 = expression2
Code language: SQL (Structured Query Language) (sql)

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'; 
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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

phone_number = NULL
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

Not equal to 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
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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; 
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > 10000
ORDER BY salary DESC;
Code language: SQL (Structured Query Language) (sql)

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 the 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;
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary >= 9000
ORDER BY salary;Code language: SQL (Structured Query Language) (sql)

Less than or equal to 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 the value of the right expression; otherwise, it returns true.

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

expression1 <= expression2Code language: SQL (Structured Query Language) (sql)

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 expression for filtering data based on a specified condition.