---
title: "SQL Where Clauses"
---

# SQL Where Clauses

> - In conjunction with a SELECT statement to filter the number of rows returned
> - As part of an UPDATE or DELETE statements to restrict which rows are manipulated by the query.

## Overview
The SQL WHERE clause is utilized to restrict the number of rows impacted by a query. A WHERE clause is commonly utilized in two scenarios:

In either scenario, the syntax of a WHERE clause is used the same, and can have multiple predicates:
```sql title="Pseudocode - Where Clause Syntax"
SELECT * FROM table WHERE column = value
```

### Where-Clauses and Data Manipulation

In most cases, statements that modify the content of a database table via UPDATE or DELETE **should** include a WHERE clause: otherwise the manipulation will be applied to every row. To demonstrate, you typically want to avoid queries that look like the following:
```sql title="Pseudocode - Never do this!"
UPDATE table SET column = 'This was a horrible mistake'
```

When manually modifying a database table, a good habit to develop involves first writing a SELECT statement. If you can successfully write a WHERE clause that only returns the results you need to modify, then you can simply change the rest of your query to manipulate the table. Thus, we could write a query like the following:
```sql title="Pseudocode - Select-Statement with a Where Clause"
SELECT * FROM table WHERE id in (100,101,150,174)
```

If we receive only the results we need to modify in the query above, we can make a simple modification to our query to now delete just the rows we want. 
```sql title="Pseudocode - Delete-Statement with a Where Clause"
DELETE FROM table WHERE id in (100,101,150,174)
```

Another common use of the WHERE clause is is to search through records and return the ones during a particular time frame. Timestamp columns can use the < and > operators to compare to each other, and certain string formats can be compared to timestamps like this query:
```sql title="Pseudocode - Where Clause with a Timestamp"
SELECT * FROM table WHERE t_stamp > '1984-01-25 16:35:55'
```

## And Operator
The AND operator allows you to specify two or more conditions in a WHERE clause, where each condition must be true for the row to be returned.
```sql title="Pseudocode - Using And to Specify Multiple Conditions"
SELECT column1, column2, column3 FROM table WHERE column1 > value AND column2 < value AND column3 = value
```

#### Example
This helps to narrow down the result set even further by adding in additional conditions that must be met. This will only return rows for customers from Germany who are also over 20 years old.
```sql title="SQL - Customers from Germany Over 20"
SELECT * FROM customers WHERE country = 'Germany' AND age > 20
```

## Or Operator
The OR operator allows you to specify two or more conditions in a WHERE clause, but only one of the conditions need to be true for the row to be returned.

```sql title="Pseudocode - Using Or to Specify Multiple Conditions"
SELECT column1, column2, column3 FROM table WHERE column1 > value OR column2 < value OR column3 = value
```

#### Example
The OR operator can help pull in data from two different subsets in the table. This will only return rows for customers from Germany or customers who are over 20 years old.
```sql title="SQL - Customers from Germany or Customers Over 20"
SELECT * FROM customers WHERE country = 'Germany' OR age > 20
```

## Not Condition
The NOT condition allows you to specify a condition that must not be met for the row to be returned.
```sql title="Pseudocode - Using Not to Specify a Condition that Shouldn't be met"
SELECT column1, column2, column3 FROM table WHERE NOT column1 = value
```

#### Example
This can be useful for finding all data other than a certain subset. This will return all customers who are not from Germany.
```sql title="SQL - Customers that are not from Germany"
SELECT * FROM customers WHERE NOT country = 'Germany'
```

## Between Operator
The BETWEEN condition allows you to specify a range of values separated by an AND that the value must be in for a condition to be true. The value can be numbers, text or dates and is inclusive of the first and last values in the range.
```sql title="Pseudocode - Using Between to Specify a Range of Values"
SELECT column1, column2, column3 FROM table WHERE column1 BETWEEN value1 AND value2
```

#### Examples
```sql title="SQL - Customers that are Between the Ages of 20 and 40"
SELECT * FROM customers WHERE age BETWEEN 20 AND 40
```

Note that the BETWEEN operator would work similarly to using a greater than or equal condition and a less than or equal condition.
```sql title="SQL - Customers that are Between the Ages of 20 and 40 with no Between Operator"
SELECT * FROM customers WHERE age >= 20 AND age <= 40
```

Timestamps can also use the BETWEEN operator to check for a given start time and end time.
```sql title="SQL - Customers that are Between the Ages of 20 and 40 with no Between Operator"
SELECT * FROM customers WHERE start_time BETWEEN '1984-01-25 00:00:00' AND '1984-01-25 16:35:55'
```

## Like Condition
The LIKE condition allows you to specify a condition that must meet a certain pattern. Typically used to compare to string values, the pattern can be built using a combination of characters and the two wildcard values.
- **%** - Used to specify any number of any characters including zero characters.
- **_** - Used to specify exactly one character.

#### Pattern Examples

| Pattern    | Meaning                                                                      | Possible Matches                                |
|--------|------------------------------------------------------------------------------|-------------------------------------------------|
| '%a%'  | Values that have an 'a' in them.                                             | 'a', 'Inductive Automation', 'almost', 'create' |
| '\_a\_'  | Values that have an 'a' with exactly one character before and after the 'a'. | 'bat', 'cat', 'can'                             |
| '_a%'  | Values that have an 'a' as the second character.                             | 'da', 'saw', 'catcher'                          |
| 'a%t' | Values that start with 'a' and end with 't'.                                 | 'about', 'at'                                   |
| '%a%\_%\_%_'| Values that contain an 'a' with at least 3 other characters after it.        | 'trains', 'airplane', 'canteen'                 |
| '%a%a%'| Values that contain at least two 'a' characters in them.                     | 'Inductive Automation', 'separate', 'apart'     |

Once the pattern has been constructed, it can be used with the LIKE operator to find values that match the specified pattern.
```sql title="Pseudocode - Using Like to Specify a Pattern of Values"
SELECT column1, column2, column3 FROM table WHERE column1 LIKE '%a%'
```

#### Example
The LIKE operator can be used to find all values that match a criteria, such as all countries with 'land' in that name
```sql title="SQL - Customers that are not from Countries with 'land' in the Name"
SELECT * FROM customers WHERE country LIKE '%land%'
```

## In Condition
The IN operator allows you to specify a subset of values, with the condition that the return match at least one of them. Using an IN operator is similar to using multiple OR operators for the same column.
```sql title="Pseudocode - Using IN to Specify Multiple Values"
SELECT column1, column2, column3 FROM table WHERE column1 IN (value1, value2, value3)
```

**Examples**

The IN can be used as a shorthand way of writing out multiple conditions for the same column separated by OR operators. This would select all values where the country is either Germany, France, or USA.
```sql title="SQL - Customers from Germany or France or USA"
SELECT * FROM customers WHERE country IN ('Germany', 'France', 'USA')
```

This would be similar to doing something like this.
```sql title="SQL - Customers from Germany or France or USA"
SELECT * FROM customers WHERE country = 'Germany' OR country = 'France' OR country = 'USA'
```

The real power of the IN operator is that instead of specifying static values, an entirely new query can be run to compare values against.
```sql title="SQL - Customers from Countries that users are also in"
SELECT * FROM customers WHERE country IN (SELECT country FROM users)
```

## Combining Multiple Operators
Multiple AND and OR operators can be combined to specify multiple different conditions that need to be met in order for a particular row to be returned. Additionally, each condition can be simple using the mathematical operators or complex using the conditions listed above. When using AND and OR operators in a WHERE clause, the AND will take precedence, evaluating first before the OR. In the pseudocode below, the row will be returned if either both the first and second conditions are met, or the third condition is met.

```sql title="Pseudocode - Using Multiple Where Clause Operators"
SELECT column1, column2, column3 FROM table WHERE column1 > value AND column2 < value OR column3 = value
```

However, the order at which the operators get evaluated can change by placing parentheses around the conditions which should be evaluated first. In the pseudocode below, the row will be returned if both the first condition is met, and either the second or third condition is met.
```sql title="Pseudocode - Using Multiple Where Clause Operators with Parentheses"
SELECT column1, column2, column3 FROM table WHERE column1 > value AND (column2 < value OR column3 = value)
```

#### Examples
We can use complex conditions with different operators to find all customers who are over the age of 50 in a country that has 'land' in the name, or any customers in Germany or France.
```sql title="SQL - Multiple Complex Conditions"
SELECT * FROM customers WHERE country LIKE '%land%' AND age > 50 OR country IN ('Germany', 'France')
```

Using parentheses in the same query can drastically change what valid return conditions are. Here, the customer must both be from a country with 'land' in the name, as well as either over 50 or from Germany or France.
```sql title="SQL - Multiple Complex Conditions"
SELECT * FROM customers WHERE country LIKE '%land%' AND (age > 50 OR country IN ('Germany', 'France'))
```