5. Shaping Data with the WHERE Clause

Overview

In this chapter, we will see how to narrow down the records returned by a query according to specific requirements. We will also see how to search for NULL values. We'll also see how to use the LIKE operator to implement pattern matching. Handling NULL values and empty cells in the results is also covered in this chapter.

Introduction

In the previous chapter, we learned how to extract data from a specific table and sort it. However, in practice, we come across a lot of scenarios where we need to filter our results based on certain criteria and conditions. Consider the example of a table containing the details of all the books available from Packt, from which we want to find the names and ISBNs of all the books that cost more than $10. This scenario is illustrated in the following figures:

Figure 5.1: Applying a filter to retrieve books that cost more than $10

The relevant columns are shown in the following table. The filtered result will look something like the second table in the following figure:

Figure 5.2: Result after filtering and retrieving books that cost more than $10

In SQL, the WHERE clause allows us to apply the filter functionality. In this chapter, we will dig deep into using the WHERE clause in our queries by combining and adding different...

The WHERE Clause Syntax

The WHERE clause is optional and can be added to any SELECT statement, usually after the FROM clause, as follows:

SELECT [COLUMNS LIST] 
FROM [TABLE NAME]
WHERE [CONDITION]
ORDER BY [COLUMN NAME] [ASC|DESC]

As you can see in the highlighted line, this statement starts with the WHERE keyword followed by a condition.

Conditions in SQL are logical operators that can be used for comparison. Condition operators are listed in the following table:

Figure 5.3: Conditional operators in SQL

These operators can be used to compare two fields/values to achieve the desired results.

Note

The compared fields or values must be of the same data type for the statement to be successful.

A simple implementation of a WHERE clause is as follows:

USE studentdemo; 
SELECT *
FROM Student;

The sample Student table looks like this:

Figure 5.4: Sample student table

Using the Student table of the StudentDemo database...

The Not Equal Operator

SQL supports the following symbols to denote the not equal operator: != and <>. The not equal operator will exclude the conditions where values are equal in the results.

Exercise 5.03: Using the != and <> Operators

The store manager realizes that the tomato sauce received has gone bad, so he does not want to present it in the list of available items. To write a query to display all the products except the tomato sauce, perform the following steps:

1. Enter the SELECT statement, using the WHERE clause and the != operator:

   SELECT ProductName,NetRetailPrice
   FROM Products
   WHERE ProductName != 'tomato sauce'
   ORDER BY NetRetailPrice;

2. Execute the query; you should see the following results:

   Figure 5.9: List of all products except tomato sauce after using the != operator

3. As an alternative, now replace the != symbol with the <> operator:

   SELECT ProductName,NetRetailPrice
   FROM Products
   WHERE ProductName <> 'tomato...

Checking for NULLS

Before we learn to handle NULL values, let's define a NULL value. NULL equals nothing in SQL, so a field that has no value is considered NULL. Usually, NULL fields are the result of having optional fields in your tables.

Checking NULL values can be done using the following two special keywords, as it cannot be done using the logical operators:

• IS NULL
• IS NOT NULL

  Note

  An important point to highlight is the fact that fields with a value of 0, or invisible characters such as spaces, are not considered NULL. NULL fields are fields that are left blank.

Exercise 5.06: Searching for NULL Values

Ideally, we want all customers' first names to be in our system. For any customer whose first name is missing from our data, we want to contact the customer and ask them about the missing information. To do this, we require a report of all the customers with missing first names:

1. Retrieve the middle name, last name, and phone number columns...

Combining Conditions with the AND, OR, and NOT Operators

On many occasions, we may need to combine multiple conditions at the same time. The best way to do this in SQL is by using the following three operators, which can be used between conditions in the WHERE clause:

• AND: This operator makes sure both sides of the operator (both conditions) are true.
• OR: This operator makes sure one side at least of the operator is true.
• NOT: This operator makes sure that the condition following this operator is false.

Exercise 5.07: Querying Multiple Conditions

Joe, a customer from LA, has requested to speak to the store manager regarding a complaint. We are going to write a query to retrieve all of this customer's details. This will allow the manager to have all the required information to best help resolve the customer's complaint.

The state code for LA is (310). To pull the required information, perform the following steps:

1. Enter the query as follows...

Summary

In this chapter, we have covered the WHERE clause in detail. We started with its syntax and then created conditions and compared data using wildcard symbols to form powerful patterns. We identified NULL values, and, at the end of the chapter, used certain operators to combine conditions. The next chapter will discuss how we can obtain data that is spread across multiple tables using Joins.