# 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;
```
***
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://notes.nomanaziz.me/development/database/sql/basics/2.-filtering-data.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
