What is IS NULL / IS NOT NULL

You can also easily filter to allow or exclude the ‘unknown’ or NULL values using IS NULL or IS NOT NULL.

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductName IS NOT NULL;

Multiple conditions

Multiple predicates can be combined with the AND and OR operators and with parentheses. However SQL Server will only process two conditions at a time. All conditions must be TRUE when connecting multiple conditions with AND operator. When using OR operator to connect two conditions, one or both may be TRUE for the result set.

For example, the following query returns product in category 2 that cost less than 10.00:

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID = 2
    AND ListPrice < 10.00;

AND operators are processed before OR operators, unless parentheses are used. For best practice, use parentheses when using more than two predicates. The following query returns products in category 2 OR 3 AND cost less than 10.00:

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE (ProductCategoryID = 2 OR ProductCategoryID = 3)
    AND (ListPrice < 10.00);

Comparison operators

Transact-SQL includes comparison operators that can help simplify the WHERE clause.

IN

The IN operator is a shortcut for multiple equality conditions for the same column connected with OR. There’s nothing wrong with using multiple OR conditions in a query, as in the following example:

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID = 2
    OR ProductCategoryID = 3
    OR ProductCategoryID = 4;

However, using IN is clear and concise, and the performance of the query won’t be affected.

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID IN (2, 3, 4);

BETWEEN

BETWEEN is another shortcut that can be used when filtering for an upper and lower bound for the value instead of using two conditions with the AND operator. The following two queries are equivalent:

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ListPrice >= 1.00
    AND ListPrice <= 10.00;

SQLCopy

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ListPrice BETWEEN 1.00 AND 10.00;

The BETWEEN operator uses inclusive boundary values. Products with a price of either 1.00 or 10.00 would be included in the results. BETWEEN is also helpful when querying date fields. For example, the following query will include all product names modified between January 1, 2012 and December 31, 2012:

SQLCopy

SELECT ProductName, ModifiedDate
FROM Production.Product
WHERE ModifiedDate BETWEEN '2012-01-01' AND '2012-12-31';

ProductName

ModifiedDate

Mountain Bike Socks, M

2012-01-01 00:00:00.000

HL Mountain Frame – Silver, 42

2012-03-05 00:00:00.000

HL Mountain Frame – Silver, 38

2012-08-29 00:00:00.000

Mountain-100 Silver, 38

2012-12-31 00:00:00.000

However because we don’t specify a time range, no results are returned after 2012-12-31 00:00:00.000. To accurately include date and time, we need to include the time in the predicate:

SQLCopy

SELECT ProductName, ListPrice, ModifiedDate
FROM Production.Product
WHERE ModifiedDate BETWEEN '2012-01-01 00:00:00.000' AND '2012-12-31 23:59:59.999';

Basic comparison operators such as Greater Than (>) and Equals (=) are also accurate when only filtering by date:

SQLCopy

SELECT ProductName, ListPrice, ModifiedDate
FROM Production.Product
WHERE ModifiedDate >= '2012-01-01' 
    AND ModifiedDate < '2013-01-01';