COMPARISON OPERATORS


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 OperatorDescription
= 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. Anemployee_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, an 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. Anemployee_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, an employee_id equal to 500 would be included in the result set.

Source: www.techonthenet.com


Advertisements