# The WHERE clause and basic operators

Previously, you focused on how to refine your SQL queries by using the <var>WHERE</var> clause to filter results. In this reading, you’ll further explore how to use the <var>WHERE</var> clause, the <var>LIKE</var> operator and the percentage sign (<var>%</var>) wildcard. You’ll also be introduced to the underscore (<var>\_</var>), another wildcard that can help you filter queries.

## How filtering helps

As a security analyst, you'll often be responsible for working with very large and complicated security logs. To find the information you need, you'll often need to use SQL to filter the logs.

In a cybersecurity context, you might use filters to find the login attempts of a specific user or all login attempts made at the time of a security issue. As another example, you might filter to find the devices that are running a specific version of an application.

## WHERE 

To create a filter in SQL, you need to use the keyword <var>WHERE</var>. <var>WHERE</var> indicates the condition for a filter.

If you needed to email employees with a title of IT Staff, you might use a query like the one in the following example. You can run this example to examine what it returns:

---

```sql
SELECT firstname, lastname, title, email
FROM employees
WHERE title = 'IT Staff';
```

---

```
+-----------+----------+----------+------------------------+
| FirstName | LastName | Title    | Email                  |
+-----------+----------+----------+------------------------+
| Robert    | King     | IT Staff | robert@chinookcorp.com |
| Laura     | Callahan | IT Staff | laura@chinookcorp.com  |
+-----------+----------+----------+------------------------+
```

---

Rather than returning all records in the <var>employees</var> table, this <var>WHERE</var> clause instructs SQL to return only those that contain <var>'IT Staff'</var> in the <var>title</var> column. It uses the equals sign (<var>=</var>) operator to set this condition.

**Note:** You should place the semicolon (<var>;</var>) where the query ends. When you add a filter to a basic query, the semicolon is after the filter.

## Filtering for patterns

You can also filter based on a pattern. For example, you can identify entries that start or end with a certain character or characters. Filtering for a pattern requires incorporating two more elements into your <var>WHERE</var> clause:

- a wildcard
- the <var>LIKE</var> operator

### **Wildcards**

A **wildcard** is a special character that can be substituted with any other character. Two of the most useful wildcards are the percentage sign (<var>%</var>) and the underscore (<var>\_</var>):

- The percentage sign substitutes for any number of other characters.
- The underscore symbol only substitutes for one other character.

These wildcards can be placed after a string, before a string, or in both locations depending on the pattern you’re filtering for.

The following table includes these wildcards applied to the string <var>'a'</var> and examples of what each pattern would return.

<div class="css-1yr0py9" id="bkmrk-pattern-results-that"><table><thead><tr><th scope="col">**Pattern**

</th><th scope="col">**Results that could be returned**

</th></tr></thead><tbody><tr><td><var>'a%'</var>

</td><td><var>apple123, art, a</var>

</td></tr><tr><td><var>'a\_'</var>

</td><td><var>as, an, a7</var>

</td></tr><tr><td><var>'a\_\_' </var>

</td><td><var>ant, add, a1c</var>

</td></tr><tr><td><var>'%a'</var>

</td><td><var>pizza, Z6ra, a</var>

</td></tr><tr><td><var>'\_a'</var>

</td><td><var>ma, 1a, Ha</var>

</td></tr><tr><td><var>'%a%'</var>

</td><td><var>Again, back, a</var>

</td></tr><tr><td><var>'\_a\_'</var>

</td><td><var>Car, ban, ea7</var>

</td></tr></tbody></table>

</div>### **LIKE**

To apply wildcards to the filter, you need to use the <var>LIKE</var> operator instead of an equals sign (<var>=</var>). <var>LIKE</var> is used with <var>WHERE</var> to search for a pattern in a column.

For instance, if you want to email employees with a title of either <var>'IT Staff'</var> or <var>'IT Manager'</var>, you can use <var>LIKE</var> operator combined with the <var>%</var> wildcard:

---

```sql
SELECT lastname, firstname, title, email
FROM employees
WHERE title LIKE 'IT%';
```

---

```
+----------+-----------+------------+-------------------------+
| LastName | FirstName | Title      | Email                   |
+----------+-----------+------------+-------------------------+
| Mitchell | Michael   | IT Manager | michael@chinookcorp.com |
| King     | Robert    | IT Staff   | robert@chinookcorp.com  |
| Callahan | Laura     | IT Staff   | laura@chinookcorp.com   |
+----------+-----------+------------+-------------------------+
```

---

This query returns all records with values in the <var>title</var> column that start with the pattern of <var>'IT'</var>. This means both <var>'IT Staff'</var> and <var>'IT Manager'</var> are returned.

As another example, if you want to search through the invoices table to find all customers located in states with an abbreviation of <var>'NY'</var>, <var>'NV'</var>, <var>'NS'</var> or <var>'NT'</var>, you can use the <var>'N\_'</var> pattern on the <var>state</var> column:

---

```sql
SELECT firstname,lastname, state, country
FROM customers
WHERE state LIKE 'N_';
```

---

```
+-----------+----------+-------+---------+
| FirstName | LastName | State | Country |
+-----------+----------+-------+---------+
| Michelle  | Brooks   | NY    | USA     |
| Kathy     | Chase    | NV    | USA     |
| Martha    | Silk     | NS    | Canada  |
| Ellie     | Sullivan | NT    | Canada  |
+-----------+----------+-------+---------+
```

---

This returns all the records with state abbreviations that follow this pattern.

## Key takeaways

Filters are important when refining what your query returns. <var>WHERE</var> is an essential keyword for adding a filter to your query. You can also filter for patterns by combining the <var>LIKE</var> operator with the percentage sign (<var>%</var>) and the underscore (<var>\_</var>) wildcards.