SQL 2005+

Basic T-SQL Queries

by Richard Carr, published at http://www.blackwasp.co.uk/SQLBasicQueries.aspx

The eighth part of the SQL Server Programming Fundamentals tutorial describes the use of the SELECT statement of the structured query language. This command allows the creation of database queries that return table rows that meet specified criteria.

The NOT Operator

The NOT operator is a useful keyword that negates the value of a predicate. This can make it much simpler to express some conditions in a where clause, allowing the SELECT statement to be more readable. The operator is placed to the left of a predicate to reverse its meaning. The predicate can, if preferred, be contained within parentheses.

The statement below provides the opposite result to an earlier example. The predicate alone would cause the command the return all of the engineers earning £20 or less per hour. With the introduction of the NOT keyword, the statement returns only those engineers earning more than this amount.

SELECT
    EngineerId AS Id,
    EngineerName AS [Engineer Name],
    HourlyRate AS Cost
FROM
    Engineers
WHERE
    NOT HourlyRate <= 20

The NOT keyword is sometimes included in an operator directly. An example of this is with the IN keyword. This can be negated using the NOT IN variant, as shown below.

SELECT DISTINCT
    EngineerId
FROM
    EngineerWorkingAreas
WHERE
    AreaCode NOT IN ('HUM', 'NYO', 'SYO', 'WYO')

I mentioned earlier that predicates can return one of three possible values, these being true, false and unknown. When using NOT, an expression that evaluates to true becomes false. A false value becomes true. We will shortly see predicates that return unknown. It is important to understand that when the NOT operator is used with an unknown value, the result remains unknown.

Checking for Null Values

When a column value or an expression evaluates as NULL, this indicates that the value is undefined or empty. This is different from a value of zero or an empty string as every null value should be considered to be a different value. Any comparison of a value with NULL or of two NULL values returns a result of "unknown".

The result of this type of comparison has some effects that are unusual but that must be carefully anticipated. We can query the Customers table to demonstrate the potential pitfalls. This table contains twenty rows. Of these, only three represent business customers, so the BusinessName column in the other seventeen rows contains NULL.

If we execute a query that returns all of the rows where the business name is "Blue-Green ltd", we find that only one row matches the predicate.

SELECT
    FirstName,
    LastName,
    BusinessName
FROM
    Customers
WHERE
    BusinessName = 'Blue-Green ltd'

Developers who are new to SQL Server may expect that changing this query to use the "not equal" operator, as shown below, would reverse the results. They may expect to see the other nineteen rows. However, as the comparison returns unknown for all comparisons with NULL, only two rows are included in the result set. This means that two apparently opposite queries do not return every row when combined.

SELECT FirstName,
    LastName,
    BusinessName
FROM
    Customers
WHERE
    BusinessName != 'Blue-Green ltd'

The same effect would be seen when using the NOT operator. If the predicate returns unknown because of a comparison with NULL, negating the result still returns an unknown value so the row is excluded from the results.

IS NULL and IS NOT NULL

If you wish to test if a column or expression is null, you can use the IS NULL keyword, rather than another comparison operator. To retrieve all of the customers that have no business name specified, execute the following statement:

SELECT
    FirstName,
    LastName,
    BusinessName
FROM
    Customers
WHERE
    BusinessName IS NULL

The opposite of "IS NULL" is "IS NOT NULL". This evaluates to true only when the column value does not contain a null value. Therefore, you can find all of the customers that are associated with businesses by executing the following.

SELECT
    FirstName,
    LastName,
    BusinessName
FROM
    Customers
WHERE
    BusinessName IS NOT NULL

Using Multiple Criteria

So far in this article we have only considered WHERE clauses with a single predicate. To expand the flexibility of the criteria in a query, you can add as many predicates as required to obtain the desired results. To do so, you use the AND and the OR operators.