> For the complete documentation index, see [llms.txt](https://notes.nomanaziz.me/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://notes.nomanaziz.me/development/database/sql/basics/2.-filtering-data.md).
# 2. Filtering Data
### Where
**(*****filter rows based on a specified condition*****)**
* `SELECT` statement returns all rows from one or more columns in a table. To select rows that satisfy a specified condition, we use a `WHERE` clause
* It appears right after `FROM` clause.
* The condition must evaluate to true, false or unkown. It can be used with boolean expressions like `AND` and `OR` operators
* It order of evalutation is as follows
\*
```
```
* **NOTE: You can't use COLUMN ALIAS with WHERE clause**
#### Comparison Operators
| Operator | Description |
| ---------- | ---------------------------------------------------- |
| `=` | Equal |
| `>` | Greater than |
| `<` | Less than |
| `>=` | Greater than or equal |
| `<=` | Less than or equal |
| `<> or !=` | Not equal |
| `AND` | logitcal operator AND |
| `OR` | logical operator OR |
| `IN` | Return true if a value matches any value in a list |
| `BETWEEN` | Retruns true if a value is between a range of values |
| `LIKE` | Returns true if a value matches a pattern |
| `IS NULL` | Returns true if a value is NULL |
| `NOT` | Negate the result of other operators |
#### Example 1 (Using WHERE with IN operator)
```sql
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name IN ('Noman','Ali');
```
#### Example 2 (Using WHERE with LIKE operator)
***
```sql
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE 'Al%';
```
The % is called a wildcard that matches any string
#### Example 3 (Using WHERE with BETWEEN operator)
***
```sql
SELECT
first_name,
LENGTH(first_name) name_length
FROM
customer
WHERE
first_name LIKE 'A%' AND
LENGTH(first_name) BETWEEN 3 AND 5
ORDER BY
name_length;
```
### Limit
**(*****get a subset of rows generated by a query*****)**
* It is the optional clause of the `SELECT` statement that contraints no of rows returned by the query
* If we want to skip number of rows before returning the limited rows, we can use the `OFFSET` clause placed after the `LIMIT` clause
#### Example 1
```sql
SELECT
film_id
FROM
film
ORDER BY
film_id
LIMIT 5;
```
#### Example 2
```sql
SELECT
film_id
FROM
film
ORDER BY
film_id
LIMIT 4 OFFSET 3;
```
***
### Fetch
**(*****limit the number of rows returned by a query*****)**
* `LIMIT` clause is not a SQL-standard, `FETCH` is a SQL standard
* If you plan to make your application compatible with other database systems, you should use the `FETCH` clause because it follows the standard SQL.
* Its syntax is like this
* ```sql
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY
```
* `ROW` is the synonym for `ROWS`, `FIRST` is the synonym for `NEXT`. So you can use them interchangeably
* The `start` is an integer that must be zero or positive. By default, it is zero if the `OFFSET` clause is not specified. In case the `start` is greater than the number of rows in the result set, no rows are returned;
* The `row_count` is 1 or greater. By default, the default value of row\_count is 1 if you do not specify it explicitly.
#### Example 1
```sql
SELECT
film_id,
title
FROM
film
ORDER BY
title
FETCH FIRST 5 ROW ONLY;
```
#### Example 2
```sql
SELECT
film_id,
title
FROM
film
ORDER BY
title
OFFSET 5 ROWS
FETCH FIRST ROW ONLY;
```
### In
**(*****select data that matches any value in a list of values*****)**
* You use `IN` operator in the `WHERE` clause to check if a value matches any value in a list of values.
* The list of values can be list of literal values such as numbers, strings or a result or `SELECT` statement like this
* `value IN (SELECT column_name FROM table_name);`
#### Example 1
```sql
SELECT customer_id,
rental_id,
return_date
FROM
rental
WHERE
customer_id IN (1, 2)
ORDER BY
return_date DESC;
```
#### Example 2 (Get Customers information from customer table whose return date from rental table is '2005-05-27')
The following query returns a list of customer ids from the rental table with the return date is 2005-05-27
```sql
SELECT customer_id
FROM rental
WHERE CASE (return_date AS DATE) = '2005-05-27'
ORDER BY customer_id;
```
Because, it returns a list of values, we can use it in input of `IN` operator
```sql
SELECT
customer_id,
first_name,
last_name
FROM
customer
WHERE
customer_id IN (
SELECT customer_id
FROM rental
WHERE CASE (return_date AS DATE) = '2005-05-27'
)
ORDER BY customer_id;
```
***
### Between
**(*****select data that is a range of values*****)**
* You use the BETWEEN operator to match a value against a range of values. The following illustrates the syntax of the BETWEEN operator:
* `value BETWEEN low AND high;`
#### Example 1
```sql
SELECT
customer_id,
payment_id,
amount,
payment_date
FROM
payment
WHERE
payment_date BETWEEN '2007-02-07' AND '2007-02-15';
```
### Like
**(*****filter data based on pattern matching*****)**
* It is used for string pattern matching
* Percent sign `%` matches any sequence of zero or more characters.
* Underscore sign `_` matches any single character.
* PostgreSQL supports the `ILIKE` operator that works like the LIKE operator. In addition, the ILIKE operator matches value case-insensitively.
* PostgreSQL also provides some operators that act like the `LIKE`, `NOT LIKE`, `ILIKE` and `NOT ILIKE` operator as shown below:
\*
```
```
#### Example 1
```sql
SELECT
'foo' LIKE 'foo', -- true
'foo' LIKE 'f%', -- true
'foo' LIKE '_o_', -- true
'bar' LIKE 'b_'; -- false
```
#### Example 2
```sql
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name ILIKE 'BAR%';
```
### Is Null
**(*****check if a value is null or not*****)**
* Used for NULL comparison
#### Example 1 (Find all contacts whose phone number is NULL)
```sql
SELECT
id,
first_name,
last_name,
email,
phone
FROM
contacts
WHERE
phone = NULL;
```
***