WHERE — Operators in T-SQL

The WHERE clause is used to filter rows based on conditions. SQL Server provides several types of operators for building these conditions.

1. Comparison Operators

Used to compare two values.

=Equal to

<> or !=Not equal to

>Greater than

<Less than

>=Greater than or equal

<=Less than or equal

Example:

SELECT *
FROM Customers
WHERE CustomerID >= 10;

2. Logical Operators

Used to combine multiple conditions.

ANDBoth conditions must be true

ORAt least one condition must be true

NOTNegates a condition

Example:

SELECT *
FROM Customers
WHERE Country = 'USA' AND City = 'Seattle';

3. Range Operators

Used to filter values within or outside a range.

BETWEENValue within a range

NOT BETWEENValue outside the range

Example:

SELECT *
FROM Orders
WHERE OrderID BETWEEN 100 AND 200;

4. Membership Operators

Used to check if a value exists in a list.

INMatches any value in a list

NOT INExcludes values in a list

Example:

SELECT *
FROM Customers
WHERE Country IN ('USA', 'Canada', 'UK');

5. Pattern Matching Operators

Used to search for patterns in text.

LIKEMatches a pattern

NOT LIKEExcludes a pattern

Example:

SELECT *
FROM Customers
WHERE CustomerName LIKE 'A%';

6. NULL Operators

Used to check for missing values.

IS NULLValue is NULL

IS NOT NULLValue is not NULL

Example:

SELECT *
FROM Customers
WHERE Phone IS NOT NULL;