3.1 Filter

3.1.1 WHERE

Learning Objectives

• Describe the basics of filtering your data
• Use the WHERE clause with common operators
• Use BETWEEN clause
• Explain the concept of a NULL value

Filtering allows us to narrow the data we want to retrieve. Filtering is also used when you’re doing analysis to get very specific about the data you want to analyze as part of your model. To do this we use what’s called the WHERE clause. And the WHERE clause comes after SELECT and FROM

SELECT column_name, column_name
FROM table_name
WHERE column_name operator value;

Here is a table of common operators:

Operator	Description
=	Equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
IS NULL	Is a null value

Let’s look at some examples.

SELECT ProductName,
UnitPrice,
SupplierID
FROM Products
WHERE ProductName = 'Tofu';

In the above example, we filter on a single condition. We look at ProductName, UnitPrice and SupplierID for tofu. You can similarly look at products whose prices are greater than or equal to 75.

SELECT ProductName,
UnitPrice,
SupplierID
FROM Products
WHERE UnitPrice >= 75;

Or you can filter out all records except one value:

SELECT ProductName,
UnitPrice,
SupplierID
FROM Products
WHERE ProductName <> 'Tofu';

You can filter for a range of values:

SELECT ProductName,
Unitprice,
SupplierID,
UnitsInStock,
FROM Products
WHERE UnitsInStock BETWEEN 15 AND 80;

You can filter NULL values by IS NULL

SELECT ProductName,
UnitPrice,
SupplierID,
UnitsInStock
FROM Products
WHERE ProductName IS NULL;

3.1.2 IN, OR and NOT

Learning Objectives

• Use the IN and OR operators to filter your data and get results you want
• Differentiate between use of the IN and BETWEEN operators
• Discuss importance of order of operations
• Explain how and when to use the NOT operator

Let’s start fromIN. To use the IN operator, we need to specify a range of conditions or a set of values.

SELECT
ProductID,
UnitPrice,
SupplierID
FROM Products
WHERE SupplierID IN (9, 10, 11);

Another operator is the OR operator. An important thing to know about this is that a database management system will not evaluate the second condition in a WHERE clause if the first condition is met.

SELECT
ProductName,
ProductID,
UnitPrice,
SupplierID,
ProductName
FROM Products
WHERE ProductName = 'Tofu' OR 'Konbu';

You can use OR with AND. There is somthing you need to pay extra attention. Look at the following two examples:

• Example1:

SELECT
ProductID,
UnitPrice,
SupplierID,
FROM Products
WHERE SupplierID = 9 OR
SupplierID = 11
AND UnitPrice > 15;

• Example2:

SELECT
ProductID,
UnitPrice,
SupplierID
FROM Products
WHERE (SupplierID = 9 OR
SupplierID = 11)
AND UnitPrice > 15;

Since SQL processes the OR before the AND, in example1, SQL will stop after processing OR, so it won’t even get to the operation after AND. In order to solve that, you need to use parentheses.

Even you don’t have to use a parenthesis, but it’s always really recommended. This way you’re not relying on the default order of operations.

You can use NOT to get the supplementary part:

SELECT *
FROM Employees
WHERE NOT City='London' AND
NOT City='Seattle';

Wildcards

Have you ever come across data where you knew either the beginning or end of something, but didn’t know the rest of it? Or maybe you know that something is like something else, but slightly different. In the rest of this section, we are going to discuss the use of the wildcards and the LIKE operator. You will learn the concept of wildcards, including their advantages and disadvantages.

Wildcard is a really powerful especially for string values or text data. A wildcard is a special character used to match parts of a value. You search for a pattern of string. LIKE works for both string and numerical variables. But wildcards cannot be used for numerical data.

% Wildcards

Wildcard	Action
%carrot	Grab anything ending with the word carrot
carrot%	Grab anything after the word carrot
%carrot%	Grab anything containing the word carrot
today%happy	Grab anything that starts with today and ends with happy
t%@gmail.com	Grab gmail address that start with t

Underscore(_) wildcard matches a single character but it is not supported by DB2.

WHERE size LIKE `_carrot`

Output:

lcarrot
mcarrot
scarrot

It is identical with:

WHERE size LIKE `%carrot`

Bracket ([]) wildcard specifies a set of characters in a specific location. It does not work with all DBMS. It does not work with SQLite.

There are some downsides to using wildcards:

• Takes longer to run
• Better to use another operator (if possible) =,<,>= etc.
• Depending on the system
• Hard to read