T-SQL 101: #20 Filtering rows to return with WHERE clause predicates

I showed earlier how we can retrieve data from a table using a SELECT clause. It can also be used to determine which columns are returned, and which table the data is being retrieved from. But we don't always want all the rows to be returned. The WHERE clause fixes that.

It's important to understand that the WHERE clause limits the rows returned to ones where the expression in the WHERE clause evaluates to a logical TRUE value.

In the example above, we're returning rows where the Size column contains the string value 370ml.

What often confuses users though is the opposite. If I write:

WHERE Size <> '370ml'

then I'm trying to get all the values except 370ml. Some rows might not have a Size specified i.e. they are NULL. You might expect them to be returned because they aren't equal to 370ml, however, the comparison:

NULL <> '370ml'

doesn't return TRUE. It returns NULL. And because we said that the WHERE clause only returns rows where the expression will be TRUE, those rows aren't returned.

This means that the WHERE clause:

WHERE Size <> '370ml'

will return only rows that have a value for Size and it isn't equal to 370ml.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Leave a Reply

Your email address will not be published. Required fields are marked *