Filtering Data with SQL
Tags: sql, tutorial, beginners, datascience
Published: 2020-12-22 10:32:57

![cover](https://i.imgur.com/08o1UV1.jpg)
<span>Photo by <a href="https://unsplash.com/@devintavery?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Devin Avery</a> on <a href="https://unsplash.com/s/photos/filter?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></span>

We can use several operators and clauses for filtering data with SQL including:
- WHERE
- BETWEEN
- IN
- OR
- NOT
- LIKE

## Basic Filtering
When we need to be specific about the data we want to retrieve and reduce the number of records we retrieve to increase query performance and reduce the strain on the client application to governance limitations then we have to use the filter option to be better perform these operations.

let's assume we have a table called `accounts`:

![customers](https://dev-to-uploads.s3.amazonaws.com/i/5svd67966vv9n86mg63h.jpg)

for filtering, we often use the `WHERE` clause to perform our query:

### Syntax

```sql
SELECT column_name_1, column_name_2
FROM table_name
WHERE column_name_1 = value;
```

### WHERE Clause
there are several operators to use in the `WHERE` clause. see below table:

![operators](https://dev-to-uploads.s3.amazonaws.com/i/zk9uoe2ty1skzneqh1qp.jpg)

```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
	WHERE name = 'Walmart';
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/nd10fnr10zqyl2dwm6ld.jpg)

another example
```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE id = 1011
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/dhh2z23i7sjkkhfrpioe.jpg)

Non-matches data
```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE name <> 'Alice'
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/54b7h0nnq1k4bnohhf3d.jpg)

### Filtering with a Range of Data
```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE id BETWEEN 1001 AND 1021
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/dhkiwlpok4hmqryw0hyh.jpg)

### Filtering with no value
```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE name IS NULL
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/hft3tfwobcir9anb22up.jpg)

We have to keep in mind that the order of sequence is very important in `SQL` statements. First `SELECT` then `FROM` then `WHERE` taken place.

## Advanced Filtering with SQL
Now we have known how to filter data in SQL to meet our goal or find our exact records of data. but sometimes we need more complex filtering to get our desire records from data sources.

in this section we use:
- IN
- OR
- AND
- NOT

### The `IN` operator
First of all, let's talk about the `IN` operator. this is used to filter a range of records.

Example
```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE name IN ('Walmart', 'Apple');
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/z9rrcqv9ywyuzp6jbcom.jpg)

### The `OR` operator
Now we're going to show you the `OR` operator. that allows you to get the result when only one or both conditions are matched. In this case, DBMS tools ignore the second condition of the `WHERE` clause if the first condition match.

Example
```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE primary_poc = 'Tamara Tume' OR primary_poc = 'Jodee Lupo'
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/eunj3tii6n5g5x8xvbgh.jpg)

when you need to match both conditions to get the result then you should use the `AND` operator over `OR`.

Sometimes `IN` and `OR `can accomplish the same thing; however, generally with `IN` you don't have to think about the order in which you're placing your different conditions. Using `IN`, we can use another `SELECT` for subqueries and when talk began for the performance issue of your complex query then you can use `IN` over `OR` because `IN` executes faster than `OR`.

### `OR` with `AND`
When we use `OR` with `AND` we often make simple mistakes and get a different result.

Let's see below two examples for a better understanding:

```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE id = 1001 OR id = 1021 AND name = 'McKesson'
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/w3xglsmqvo14z8y43kbw.jpg)

Agian

```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE (id = 1001 OR id = 1021) AND name = 'McKesson'
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/voq8xd2uqpcgbinj7ila.jpg)

You may see different results in two queries. because here SQL performs OR before AND and the first condition is true then omit other conditions. In the second example, we use parenthesis to separate AND and OR conditions to get the actual result.

Note: SQL order of operation is AND before OR. but here you write OR before AND and OR first condition is true so, rest of the condition is omitted.

### The `NOT` operator
Finally, we are here to talk about the `NOT` operator. when we want some result that does not contain a specific thing then we can use the `NOT` operator the get the result.

```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE primary_poc IS NOT NULL AND NOT name = 'Apple'
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/qydodkxb9panc22sbd3l.jpg)

## Using `Wildcards` to Filter data
Sometimes we have to deal with string data and you don't know the whole string or maybe you are looking for something that starts with an alphabet/word or ends with a specific word/alphabet. that's why we use the `Wildcards` operator to perform filtering with SQL.

we can use 
- Percentage(%)
- Underscore(_)
- Bracket [] 
as a Wildcard operator. but keep in mind that each special character is not supported by all DBMS tools.

- DB2 does not support Underscore(_)
- SQLite does not support Bracket[]

Let's see some example:

```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE name LIKE 'A%'; -- name starts with 'A'
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/hpj9el3yvjs0hhd0t2wp.jpg)


again

```sql
SELECT
	id,
	name,
	website,
	primary_poc
FROM accounts
  WHERE name LIKE '%art'; -- name ends with 'art'
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/0lz3mkfyyfczszmbhrfm.jpg)

there are lots of tips and tricks out there over the internet. you can find them just simpling googling. I just want to share with you the basic concept to understand.

Thanks for reading.

Note: I would love to see your comments and accepting your valuable thought.