BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Basic T-SQL Queries

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.

AND Operator

The AND operator combines two predicates using a logical AND operation. However, as the predicates in a query can be evaluated as true, false or unknown, the results are not as simple as for pure Boolean logic. The table below shows the possible outcomes of combining these values. Remember that rows are returned if the outcome is true and omitted from the result set if false or unknown.

Operand 1Operand 2Result
truetruetrue
truefalsefalse
falsetruefalse
falsefalsefalse
trueunknownunknown
falseunknownfalse
unknowntrueunknown
unknownfalsefalse
unknownunknownunknown

Let's use the AND keyword to perform a SELECT that requires that two predicates return true. In this case, we will obtain all of the rows in the Customers table where the customer is not linked to a business and was created on or after 1 January 2008.

SELECT
    FirstName,
    LastName,
    BusinessName,
    CreatedDate
FROM
    Customers
WHERE
    BusinessName IS NULL
AND
    CreatedDate >= '2008-01-01'

OR Operator

The OR operator combines predicates using a logical OR operation. This allows you to build a result set containing results that meet at least one of several criteria. Again, consideration must be given to the effects of a predicate being unknown.

Operand 1Operand 2Result
truetruetrue
truefalsetrue
falsetruetrue
falsefalsefalse
trueunknowntrue
falseunknownunknown
unknowntruetrue
unknownfalseunknown
unknownunknownunknown

We can modify the previous query to use the OR operator instead of AND. This time, we will obtain all of the rows in the Customers table where the customer is not linked to a business or where the customer was created on or after 1 January 2008.

SELECT
    FirstName,
    LastName,
    BusinessName,
    CreatedDate
FROM
    Customers
WHERE
    BusinessName IS NULL
OR
    CreatedDate >= '2008-01-01'

Combining AND and OR

Although the examples shown above only use two predicates in their criteria, you can add as many predicates as required. This may include the use of AND and OR operators in the same WHERE clause. In these cases, it is important to know the order in which these operators will be considered. SQL Server provides a simple rule to determine the order. Firstly, all of the AND operations are evaluated. Afterwards, the OR operations are processed to obtain the final result. If you wish to modify this order, you can include parentheses:

SELECT
    FirstName,
    LastName,
    BusinessName,
    CreatedDate
FROM
    Customers
WHERE
    BusinessName IS NOT NULL
AND
    (FirstName LIKE 'A%' OR CreatedDate >= '2008-01-01')

Sorting Data

The final clause to be considered in this article is ORDER BY. When added to the end of a SELECT statement, the clause specifies the columns that should be used to sort the results of the query. The results may be sorted by a single column or by multiple columns specified in a comma-separated list. For example, we can sort the customers by business name and then last name using the following query. If the business names match, the matching rows are sorted by last name. NB: Note that NULL values appear first in the list.

SELECT
    FirstName,
    LastName,
    BusinessName,
    CreatedDate
FROM
    Customers
ORDER BY
    BusinessName,
    LastName

Adding the DESC keyword to one or more of the columns reverses the sort order. This time, we will sort the customers by business name in descending order. For duplicate business names, we will sort by first name in ascending order. NB: Note the use of the ASC keyword for ascending order sorting. This is optional as ascending order is the default.

SELECT
    FirstName,
    LastName,
    BusinessName,
    CreatedDate
FROM
    Customers
ORDER BY
    BusinessName DESC,
    LastName ASC
3 August 2008