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.
