# The WHERE statement and operators

The **WHERE **statement is one of the most commonly used statements when writing SQL queries. It is used in filtering data from a table. The **WHERE **statement filters data when it meets a certain condition or criteria. 

YIf you were asked to "*Select every student who owns more than 5 apples*"

Using a **WHERE ** statement, this will be written as 

```
SELECT students, apples
FROM class
WHERE apples > 5
``` 
This means that rows will only be selected if the apple value of that row is greater than 5. The **WHERE **statement can work with all the comparison operators. These include:

1.  greater than >

2. less than <

3. greater than or equal to >=

4. less than or equal to <=

5. equal to =

6. not equal to !=


The **WHERE **statement can also work with logical operators (**AND, OR, LIKE, IN, BETWEEN, and NOT**). Logical operators combined with the **WHERE **statement can be used to select rows based on multiple criteria. They can also be used in conjunction with comparison operators. 

#### AND OPERATOR
The **AND** operator is used to select rows based on the rows meeting all criteria specified. For example, you work as a data analyst at a restaurant and your boss wants to get a list of customers that bought food worth $1000 and above **AND ** have also visited the restaurant more than 3 times. Your **WHERE ** statement will look like this


```
SELECT *
FROM customers
WHERE total_amt_spent >= 1000 AND visits >= 3
``` 

This query selects all columns and rows from the 'customers' table **WHERE** the 'total_amt_spent' row is equal to or greater than (>=) $1000 **AND ** the number of visits is equal to or greater than (>=) 3. Using the **AND **statement, if one of the criteria fails, that row will not be selected. So if a row meets the $1000 criteria but only contains 2 visits, the row will not be selected.


#### OR OPERATOR
The **OR** operator is used to select rows if they meet any of the listed criteria. 
For example, you work as a data analyst at a restaurant and your boss wants to get a list of customers that bought food worth $1000 and above **OR ** have visited the restaurant more than 3 times. Your **WHERE ** statement will look like this

```
SELECT *
FROM customers
WHERE total_amt_spent >= 1000 OR visits >= 3
``` 

This query selects all columns and rows from the 'customers' table **WHERE** the 'total_amt_spent' row is equal to or greater than (>=) $1000 **OR** the number of visits is equal to or greater than (>=) 3. Using the **OR **statement, the rows just needs to meet one of the criteria and it will be selected


#### LIKE OPERATOR
When working with text, the LIKE operator comes in handy. The LIKE operator makes use of the '%' symbol. The '%' symbol indicates that we may desire any amount of characters before or after a certain set of characters. Because lower and uppercase characters are not the same within the string, you must specify i.e letter 'T' differs from searching for the letter 't'. You can use single or double quotes  The '%' symbol can come before or after the search word depending on what you're looking for.


```
SELECT name
FROM customers
WHERE name LIKE '%s';
``` 
This query returns all names that end in 's'. The '%' symbol indicates that any number of text can appear before the 's'. If the '%' symbol came after the 's', it would return all names that begin with 's' irrespective of the number of text that comes after it. 

The '%' symbol can also be put at both ends if perhaps you're searching for all fields that contain some specific characters - WHERE name LIKE '%https%'; if we want to retrieve all web addresses that have 'https' in them. 

The **LIKE** operator only works with text except when used in conjunction with CAST to perform operations on numeric data; this is beyond the scope of this article

#### IN OPERATOR
When working with numeric and text columns, the **IN** operator comes in handy. You can use a '=' operator when working with single values but when you want to check multiple values, it's advisable to use the **IN** operator. The **IN** operator simply says, if any value in the list is present in the rows, select the row. The **OR** operator can also be used for multiple values, but the **IN** operator is a cleaner method to construct these queries.
```
SELECT *
FROM orders
WHERE meal IN ('tasty shrimps', 'spicy snails', 'roasted beef');
``` 
This query selects all rows from the orders table where customers ordered any of the 3 meals. If you were to write an **OR** query, it would be 

```
SELECT *
FROM orders
WHERE meal = 'tasty shrimps' OR meal =  'spicy snails' OR meal = 'roasted beef'
```
Now, we see why we use the **IN** operator 


#### BETWEEN OPERATOR
The **BETWEEN** operator lets you specify a range of numeric values. It can be used to replace **AND** statements when dealing with the same column

```
SELECT * 
FROM orders
WHERE total_amt_spent BETWEEN 1000 AND 2000;
``` 
If you were to use the **AND** operator, it will look like this


```
SELECT occurred_at, gloss_qty 
FROM orders
WHERE gloss_qty >= 1000 AND gloss_qty <= 29;
``` 

In **AND** statements, the column name has to be specified for every criterion. In **BETWEEN** statements, the column name doesn't have to be specified more than once as stated in the example above. It's a simpler way of using the **AND ** operator on a single column.


####NOT OPERATOR
The **NOT** operator comes in handy when working with the two of the operators discussed above: **IN** and **LIKE**. We can get all of the rows that don't satisfy a specific condition by specifying **NOT LIKE** or **NOT IN**.


```
SELECT name
FROM customers
WHERE name NOT LIKE '%s';
``` 

This query returns every name that doesn't end in 's'

```
SELECT *
FROM orders
WHERE meal NOT IN ('tasty shrimps', 'spicy snails', 'roasted beef');
``` 
This query returns every row that doesn't contain any of these: 'tasty shrimps', 'spicy snails', or 'roasted beef'. 
The **NOT**operator is for exclusion

All these operators talked about can be used in tandem as the business case needs. We just need to get our creative juices flowing. We are SQL engineers lol. 

We have come to the end of this article. I hope you gained as much as I did while writing it. 

You can share this article if it has helped you or you know someone it may help. Thank you in advance.

Till we see again,

Koms