SQL WHERE

The SQL WHERE clause works in conjunction with other SQL clauses like SELECT, INSERT and UPDATE to specify a search condition for these statements. We are going to give an example of the SQL WHERE clause used along with the SQL SELECT clause:

SELECT AverageTemperature FROM Weather WHERE City = 'New York'

The first 2 lines of the SELECT SQL statement above are already familiar from the previous chapter, but the 3rd line is new and specifies the SQL WHERE search condition:

WHERE City = 'New York'

If we translate the SQL statement to plain English, it would sound like this "Select the average temperature for the city of New York"

The result of the added SQL WHERE condition will be:

AverageTemperature
22 C

We can have more than one search condition after the SQL WHERE clause. To illustrate this we will put a couple of more entries in the weather table:

CityAverageTemperatureDate
New York22 C10/10/2005
Seattle21 C10/10/2005
Washington20 C10/10/2005
New York18 C10/09/2005
Seattle20 C10/09/2005
Washington17 C10/09/2005


SELECT * FROM Weather WHERE AverageTemperature > = 20 AND City <> 'New York'

The first 2 lines of the above SQL expression instruct the SQL engine to retrieve all the columns from the Weather table. The 3rd line is the line that defines what conditions needs to be met for a row to be returned in our result set. This time we have 2 search conditions after the SQL WHERE clause - the first condition limits the rows to those with AverageTemperature greater or equal to 20 C and the second condition limits the rows to only those where the City column is different than 'New York'. As you may have noticed the 2 SQL WHERE conditions are concatenated with the AND SQL keyword, meaning that both conditions have to be met for all rows returned in the result set.

The result of this SQL WHERE clause condition is below:

CityAverageTemperatureDate
Seattle21 C10/10/2005
Washington20 C10/10/2005
Seattle20 C10/09/2005

What will happen if we concatenate the 2 SQL WHERE search condition with the OR SQL keyword instead of the AND SQL keyword?

SELECT * FROM Weather WHERE AverageTemperature > = 20 OR City <> 'New York'


CityAverageTemperatureDate
New York22 C10/10/2005
Seattle21 C10/10/2005
Washington20 C10/10/2005
Seattle20 C10/09/2005
Washington17 C10/09/2005

As you can see this time we have 2 more rows returned (in green) compared with the previous SQL WHERE clause results. Why has this happened? This time we have used the OR SQL keyword, which means that every row which satisfies at least one of the 2 SQL WHERE conditions will be returned. The first row doesn't satisfy the condition for the City, but satisfy the average temperature condition that's why it shows up in the final result set. The last row satisfy the City condition, but not the average temperature condition, and because we need to meet at least one of the 2 SQL WHERE conditions this rows is presented in the result set too.