{"id":1204,"date":"2018-01-16T06:31:27","date_gmt":"2018-01-16T13:31:27","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1204"},"modified":"2025-01-19T02:14:40","modified_gmt":"2025-01-19T09:14:40","slug":"sql-comparison-operators","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-comparison-operators\/","title":{"rendered":"SQL Comparison Operators"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about SQL comparison operators and how to use them to form conditions for filtering data.<\/p>\n\n\n\n

The SQL comparison operators allow you to test if two expressions are the same. <\/p>\n\n\n\n

The following table illustrates the comparison operators in SQL:<\/p>\n\n\n\n

Operator<\/th>Meaning<\/th><\/tr><\/thead>
=<\/td>Equal<\/td><\/tr>
<><\/td>Not equal to<\/td><\/tr>
><\/td>Greater than<\/td><\/tr>
>=<\/td>Greater than or equal to<\/td><\/tr>
<<\/td>Less than<\/td><\/tr>
<=<\/td>Less than or equal to<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The result of a comparison operator has one of three value true, false, and unknown.<\/p>\n\n\n\n

Equal to operator(=) #<\/a><\/h2>\n\n\n\n

The equal to operator compares the equality of two expressions:<\/p>\n\n\n

expression1 = expression2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns true if the value of the left expression is equal to the value of the right expression; otherwise, it returns false.<\/p>\n\n\n\n
For example, the following statement finds the employee whose last name is Himuro<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  employee_id,\n  first_name,\n  last_name\nFROM<\/span>\n  employees\nWHERE<\/span>\n  last_name = 'Himuro'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, the query searches for the string Himuro<\/code> in the last_name<\/code> column of the employees<\/code> table.<\/p>\n\n\n\n
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:<\/p>\n\n\n
SELECT<\/span>\n  employee_id,\n  first_name,\n  last_name,\n  phone_number\nFROM<\/span>\n  employees\nWHERE<\/span>\n  phone_number = NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, it returns an empty result set because the following expression always returns false.<\/p>\n\n\n
phone_number = NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    employee_id, first_name, last_name, phone_number\nFROM<\/span>\n    employees\nWHERE<\/span>\n    phone_number IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Not equal to operator (<>) #<\/a><\/h2>\n\n\n\n
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.<\/p>\n\n\n
expression1 <> expression2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement returns all employees whose department id is not 8.<\/p>\n\n\n
SELECT<\/span>\n  employee_id,\n  first_name,\n  last_name,\n  department_id\nFROM<\/span>\n  employees\nWHERE<\/span>\n  department_id <> 8<\/span>\nORDER<\/span> BY<\/span>\n  first_name,\n  last_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
You can use the AND<\/a><\/code> 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.<\/p>\n\n\n
SELECT<\/span>\n  employee_id,\n  first_name,\n  last_name,\n  department_id\nFROM<\/span>\n  employees\nWHERE<\/span>\n  department_id <> 8<\/span>\n  AND<\/span> department_id <> 10<\/span>\nORDER<\/span> BY<\/span>\n  first_name,\n  last_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Greater than operator (>) #<\/a><\/h2>\n\n\n\n
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.<\/p>\n\n\n
expression1 > expression2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n  employee_id,\n  first_name,\n  last_name,\n  salary\nFROM<\/span>\n  employees\nWHERE<\/span>\n  salary > 10000<\/span>\nORDER<\/span> BY<\/span>\n  salary DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
You can combine expressions that use various comparison operators using the AND<\/a><\/code> or OR<\/a><\/code> operator. For example, the following statement finds employees in department 8 and have the salary greater than 10,000:<\/p>\n\n\n
SELECT<\/span> \n    employee_id, first_name, last_name, salary\nFROM<\/span>\n    employees\nWHERE<\/span>\n    salary > 10000<\/span> AND<\/span> department_id = 8<\/span>\nORDER<\/span> BY<\/span> salary DESC<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Less than operator (<) #<\/a><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
The following shows the syntax of the less than operator:<\/p>\n\n\n
expression1 < expression2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the statement below returns all employees whose salaries are less than 10,000:<\/p>\n\n\n
SELECT<\/span>\n  employee_id,\n  first_name,\n  last_name,\n  salary\nFROM<\/span>\n  employees\nWHERE<\/span>\n  salary < 10000<\/span>\nORDER<\/span> BY<\/span>\n  salary DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Greater than or equal operator (>=) #<\/a><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
The following illustrates the syntax of the greater than or equal operator:<\/p>\n\n\n
expression1 >= expression2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement finds employees whose salaries are greater than or equal 9,000:<\/p>\n\n\n
SELECT<\/span>\n  employee_id,\n  first_name,\n  last_name,\n  salary\nFROM<\/span>\n  employees\nWHERE<\/span>\n  salary >= 9000<\/span>\nORDER<\/span> BY<\/span>\n  salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
Less than or equal to operator(<=) #<\/a><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
The following shows the syntax of the less than or equal to operator:<\/p>\n\n\n
expression1 <= expression2<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement finds employees whose salaries are less than or equal to 9,000:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the comparison operators to form comparison expression for filtering data based on a specified condition.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
For example, to find the employees whose salary is greater than 10,000, you use the greater than operator in the WHERE<\/a><\/code> clause as follows:<\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
To compare null values, you use the IS NULL<\/a><\/code> operator instead:<\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n