SQL Server Comparison Operators

This SQL tutorial focuses on the SQL Server Comparison Operators, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the SQL WHERE clause in SQL Server. To read additional posts regarding this subject, please use the following links:

• Introduction – SQL Server WHERE clause
• Comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

SQL Server Comparison Operators

Comparison conditions in SQL Server are used to compare one expression to another value or expression. This section explains how to use the comparison conditions (as mentioned earlier,  = , < , > , => , =<, <>) .

Comparing Numbers

SELECT *
FROM employees
WHERE  salary  =  500

In this SQL Server example, the explicit value of 500 is compared to the salary value in the Salary column of the Employees table.

SELECT *
FROM employees
WHERE  DepartmentID  =  90

In this SQL Server example, the explicit value of 90 is compared to the department number in the DepartmentID column.

SELECT *
FROM employees
WHERE  age  =  27

In this SQL Server example, the explicit value of 27 is compared to the age in the Age column.

It is not possible to write the numeric value together with special characters. also, numeric values should not be enclosed within single quotes.

SELECT *
FROM employee
WHERE  salary =    $50000
-- (error)

SELECT * 
FROM employees 
WHERE raise_percent = %20 
-- (error) 
-- * Usually these type of numbers are expressed as a number with a decimal point
-- * For example 0.2

SELECT *
FROM employees
WHERE  salary  =   50,000
-- (error)

SELECT *
FROM employees
WHERE salary   =   50000
-- (valid comparison)

If the requested comparison does not match table values (for example, the highest salary in the table is 12,000 and you would like to display the employees whose salary is greater than 20,000), no results will be retrieved and no error will be generated either.

Comparing Strings

In SQL Server the string value must be enclosed within single quotes (‘string value’).

SELECT * 
FROM
WHERE  last_name = Smith
-- (error)

SELECT * 
FROM employees
WHERE  last_name = 'Smith'
-- (valid comparison)

SELECT * 
FROM employees
WHERE department_name = 'Sales'
-- (valid comparison)

In SQL Server the string value are not case sensitive.  The following SQL Server queries will produce exactly the same results:

SELECT last_name
FROM employees
WHERE last_name = 'SmiTH' 

SELECT last_name
FROM employees
WHERE last_name = 'Smith'

SELECT last_name
FROM employees
WHERE last_name = 'SMITH'

Comparing Dates

In SQL Server comparing with dates requires specifying the date value within single quotes (‘date value’). It also requires using the appropriate SQL Server date format, as date values are format sensitive.

SELECT *
FROM employees
WHERE  hire_date  = 05-17-2009
-- (error - no single quotes)

SELECT *
FROM employees
WHERE hire_date = '05-JAN-2009'
-- (error - not a valid date format)    

SELECT *
FROM employees
WHERE hire_date = '2009-05-17'
-- (valid comparison)

SQL Server Comparison Operators Limit

In SQL Server Comparison operators are limited to a single value. For example, if you would like to know the names of the employees whose salary equals 5000, 6000, 8500, or 10,000, you cannot use the following SQL Server WHERE clause:

SELECT first_name
FROM employees
WHERE salary = 5000 , 6000 , 8500 , 10,000 
-- (error)