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)
The SQL Logical Operators address this matter (IN, BETWEEN, LIKE, IS NULL, etc.)