Sql Server Comparison Operators

SQL Server: Comparison Operators

In this post explores all of the comparison operators used to test for equality and inequality, as well as the more advanced operators in SQL Server (Transact-SQL).

Description

Comparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison operators that you can use in SQL Server (Transact-SQL):

Comparison Operator Description
= Equal
> Not Equal
!= Not Equal
> Greater Than
>= Greater Than or Equal
Less Than
= Less Than or Equal
!> Not Greater Than
! Not Less Than
IN ( ) Matches a value in a list
NOT Negates a condition
BETWEEN Within a range (inclusive)
IS NULL NULL value
IS NOT NULL Non-NULL value
LIKE Pattern matching with % and _
EXISTS Condition is met if subquery returns at least one row

There are many comparison operators in SQL Server and Transact-SQL. Let's explore how to use the more common operators.

Example - Equality Operator

In SQL Server, you can use the = operator to test for equality in a query.

For example:

SELECT * FROM employees WHERE first_name = 'Jane';

In this example, the SELECT statement above would return all rows from the employees table where the first_name is equal to Jane.

Example - Inequality Operator

In SQL Server, you can use the > or != operators to test for inequality in a query.

For example, we could test for inequality using the > operator, as follows:

SELECT * FROM employees WHERE first_name > 'Jane';

In this example, the SELECT statement would return all rows from the employees table where the first_name is not equal to Jane.

Or you could also write this query using the != operator, as follows:

SELECT * FROM employees WHERE first_name != 'Jane';

Both of these queries would return the same results.

Example - Greater Than Operator

You can use the > operator in SQL Server to test for an expression greater than.

SELECT *
FROM employees
WHERE employee_id > 3000;

In this example, the SELECT statement would return all rows from the employees table where the employee_id is greater than 3000. An employee_id equal to 3000 would not be included in the result set.

Example - Greater Than or Equal Operator

In SQL Server, you can use the >= operator to test for an expression greater than or equal to.

SELECT *
FROM employees
WHERE employee_id >= 3000;

In this example, the SELECT statement would return all rows from the employees table where the employee_id is greater than or equal to 3000. In this case, n employee_id equal to 3000 would be included in the result set.

Example - Less Than Operator

You can use the operator in SQL Server to test for an expression less than.

SELECT *
FROM employees
WHERE employee_id  500;

In this example, the SELECT statement would return all rows from the employees table where the employee_id is less than 500. An employee_id equal to 500 would not be included in the result set.

Example - Less Than or Equal Operator

In SQL Server, you can use the = operator to test for an expression less than or equal to.

SELECT *
FROM employees
WHERE employee_id = 500;

In this example, the SELECT statement would return all rows from the employees table where the employee_id is less than or equal to 500. In this case, n employee_id equal to 500 would be included in the result set.

Example - Advanced Operators

For the more advanced comparison operators, we've written specific tutorials to discuss each one individually. These topics will be covered later, or you can jump to one of these tutorials now.