Network Computingwww.networkcomputing.com

Suppose we have been asked to produce a report of the customer ID and name of all customers from the state of Washington.

1. We know that we need the Customers table as the basis for our query. We also know that we need the ID and name fields (CustomerID and CompanyName). With this information we can write the base query:

SELECT CustomerID, CompanyName
FROM Customers

1. Now, we want to filter our data so that we only display the Washington customers. We will need to filter on the Region field in the database for all values of 'WA'. Since we are looking for an exact match, we should use the equals operator in our WHERE clause. With this information, we can now finish our query:

SELECT CustomerID, CompanyName
FROM Customers
WHERE Region = 'WA'

Click here to enlarge

When we use the WHERE clause in a query, we instruct SQL to filter the records based on the criteria in the WHERE clause. In our example, we are telling the query to filter for all of the records where the Region field contains only the string 'WA'. All other rows will be ignored. Each condition in a WHERE clause acts as a filter on the records from a field. WHERE clauses can be as complex as we need them to be, and we will be exploring their use throughout the book.

Following are some errors and error messages you may come across when constructing some of these statements:

A NULL value in SQL represents an unknown entity. This is significantly different from an empty value. An empty value tells us that we know that there is no data, but a NULL value means that we don't know what value should be in the field, thus NULL is considered to be an unknown value. NULL values are treated as special items in SQL. We can see this in the following simple comparison between NULL and empty in SQL Server.

First, let's test to see if an empty value equals another empty value:

 
 IF '' = ''
 PRINT 'TRUE'
ELSE
 PRINT 'FALSE'

TRUE

IF NULL = NULL
 PRINT 'TRUE'
ELSE
 PRINT 'FALSE'

FALSE

Try it Out - Working with NULL Values

We would like to find a list of all of our customers that are not located in the US and Canada.

2. Remember the Region field that holds state information for only US and Canada customers? Well in the Northwind database, all other international customers have a NULL value in this field. Using this as the basis of our query, we know that we want the company name from the Customers table filtered where the Region field is NULL as this would output the names of customers outside the US and Canada.

2. Enter the following SQL statement:

SELECT CompanyName
FROM Customers
WHERE Region IS NULL