<jupyter_start><jupyter_text>2 Filtering dataIn chapter 1, we have seen how to retrieve data with select. Now you may want to know how can we only retrieve the data that interest us,but not all the rows. We can use the **where** statement to filter data that satisfied certain conditions.<jupyter_code>%load_ext sql
%config SqlMagic.autocommit=False
%config SqlMagic.autolimit=20
%config SqlMagic.displaylimit=20
%sql postgresql://user-pengfei:gv8eba5xmsw4kt2uk1mn@postgresql-124499/test
%%sql
SELECT * from orders limit 5;<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.<jupyter_text>2.1 Filtering digit columnsTo filter digit column, we need to build a boolean expression such as column_name comparator valuePossible comparators:- equality : =- inequality : !=, or - greater than: >- less than : <- Greater than equal to : >=- Less than equal to : <=Below query is an example, in the boolean expression column name is order_date (we extract the year from the date), comparator is =, value is 1996This query should only return records where the order_date equals to 1996<jupyter_code>%%sql
select * from orders where extract(year from order_date)=1996 limit 5<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.<jupyter_text>What if I want to get all records where year is not equals to 2010. There are two possible ways (!=, or ) to express inequality. Most database servers such as Mysql, Postgresql, SQLite, etc. support both. However, some database server such as **Microsoft Access and IBM DB2 only support **.<jupyter_code>%%sql
select * from orders where extract(year from order_date)!=1996 limit 5
%%sql
select * from orders where extract(year from order_date)<>1996 limit 5<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>These two queries do the same thing. You should see the same output. We can also qualify inclusive ranges using a BETWEEN statement, as shown here(“inclusive” means that 1996 and 1997 are included in the range):<jupyter_code>%%sql
select *
from orders
where extract(year from order_date) between 1996 and 1997
limit 5<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>2.2 Combining multiple filtering condition 2.2.1 And operatorWe can express the "between and" range with another expression. For instance the year must be greater than or equal to 2005 and less than or equal to 2010. We need to add two filter and combine the result of the two filter with an **and**.Below query should return exactly the same result as the above query<jupyter_code>%%sql
select *
from orders
where extract(year from order_date) >= 1996 and extract(year from order_date)<= 1997
limit 5<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.<jupyter_text>Note the "between and" express an inclusive range, for not inclusive range, we can not use it. As a result, evaluate the and of two filters become very useful. Below query returns orders that order_date > 1996 and < 1998.<jupyter_code>%%sql
select *
from orders
where extract(year from order_date) > 1996 and extract(year from order_date)< 1998
limit 5<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>2.2.2 Or operatorThe OR operator will return the record if at least one of the criteria is true for the record. For instance, if we wanted only orders with months 3, 6, 9, or 12, we can use below query:<jupyter_code>%%sql
select * from orders
where extract(month from order_date)=3
or extract(month from order_date)=6
or extract(month from order_date)=9
or extract(month from order_date)=12
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>2.2.3 In operatorIn the above example, we tested column month with a list of possible value. In this kind of situation, we can use the **In** operator.<jupyter_code>%%sql
select * from orders
where extract(month from order_date) in (3,6,9,12)
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>We can also express the negation of the in operator by adding not in front of **in** operator.<jupyter_code>%%sql
select * from orders
where extract(month from order_date) not in (3,6,9,12)
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>2.2.4 Arithmetic operatorsWe can notice 3,6,9,12 can all be divided by 3. So we can also use another way to get the same records as above examples.**Note some database such as Oracle does not support the modulus operator. It instead uses the MOD() function.**<jupyter_code>%%sql
select * from orders
where cast(extract(month from order_date) as integer ) %3=0
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>2.3 Filtering text columnsThe rules for qualifying text fields follow the same structure, although there are subtle(small) differences. You can**use =, AND, OR, and IN statements with text**. However, when using text, you must wrap literals (or text values you specify) **in single quotes**.<jupyter_code>%%sql
SELECT * FROM orders
WHERE ship_city = 'Paris'
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
4 rows affected.<jupyter_text>Note **postgresql does not support double quote** on string value. So below query will return error in postgresql. It works for Mysql or Sqlite<jupyter_code>%%sql
SELECT * FROM orders
WHERE ship_city = "Paris"
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
(psycopg2.errors.UndefinedColumn) column "Paris" does not exist
LINE 2: WHERE ship_city = "Paris"
                          ^

[SQL: SELECT * FROM orders
WHERE ship_city = "Paris"
limit 5;]
(Background on this error at: https://sqlalche.me/e/14/f405)<jupyter_text>If we do not add " or ' on Paris, the database server will get confused and think Paris is a column name rather than a text value. This single-quote rule applies to all text operations. For example, below query will return error.<jupyter_code>%%sql
SELECT * FROM orders
WHERE ship_city = Paris
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
(psycopg2.errors.UndefinedColumn) column "paris" does not exist
LINE 2: WHERE ship_city = Paris
                          ^

[SQL: SELECT * FROM orders
WHERE ship_city = Paris
limit 5;]
(Background on this error at: https://sqlalche.me/e/14/f405)<jupyter_text>We can also use string value in other filter operations. Below query will return all rows that the ship_city is Paris, London, or Madrid.<jupyter_code>%%sql
SELECT * FROM orders
WHERE ship_city in ('Paris','London','Madrid')
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>2.3.1 Other useful functions filter by using length**length** operator can get the length of a text field. For instance, below query will filter all rows that ship_country length is <=2 (e.g. UK).<jupyter_code>%%sql
SELECT * FROM orders
WHERE length(ship_country) <= 2
limit 5<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>Wild card text filtering with like operatorAnother common operation is to use wildcards with LIKE followed by a regular expression, in the regular expression:- % : means any number of characters an- _ : means any single character.- Any other character is interpreted literally.So, if you wanted to find all orders that have ship_country start with the letter “C” you would run below query to find all text start with “C” and followed by any characters<jupyter_code>%%sql
select * from orders
where ship_country like 'C%'
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>If you want find all text that have a “C” as the first character and a “n” as the third character, you can run below query.**Note the character inside '' is  case-sensitive unlike the query command**. Try change below query to 'C_N' and see what happens.<jupyter_code>%%sql
select * from orders
where ship_country like 'C_n%'
limit 5;<jupyter_output>* postgresql://pliu:***@127.0.0.1:5432/north_wind
5 rows affected.<jupyter_text>2.4 Handling nullYou may have noticed that in table orders the ship_region column have null values. **A null is a value that has no value. It is the complete absence of any content. It is a vacuous state**.In sql, **Null values cannot be determined with an = . You need to use the IS NULL or IS NOT NULL statements to identify null values**Below query returns all rows that snow_depth is null<jupyter_code>%%sql
select * from orders
where ship_region is null
limit 5;<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.<jupyter_text>2.4.1 Why we even have null values in the database? The null values is useful for some use case. For example, for a table of weather station, the column such as snow_depth or precipitation, it does make sense. Not because it was a sunny day (in this case, it is better to record the values as 0), but rather because some stations might not have the necessary instruments to take those measurements. Can we replace null by 0 for snow_depth?No, It might be misleading to set those values to 0 (which implies data was recorded), so those measurements should be left null.In some columns, we can not have null values. For example, the **order_id** column should be designed that it never allows nulls. Because if it's null, all the rest columns of this row become orphan data that belongs to no order.We can see that nulls values are ambiguous and it can be difficult to determine their business meaning. It is important that nullable columns (columns that are allowed to have null values) **have documented what a null value means from a business perspective**. **Otherwise, nulls should be banned from those table columns**.Do not confuse nulls with empty text(i.e., '' ). This also applies to whitespace text (i.e., ' ') . These will be treated as values andnever will be considered null. A null is definitely not the same as 0 either, because 0 is a value, whereas null is an absence of a value. 2.4.2 Problems caused by null valuesWe know that freight column has null values, try to run the following query. You can notice that the returned rows do not contain any null values. Because null is not 0 or any number, it will not qualify to any condition. So the **freight <= 6** filtered out all rows that contain null value.<jupyter_code>%%sql
SELECT order_id, freight FROM orders
WHERE freight <= 6
limit 5<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.<jupyter_text>But having a null value does not mean the freight > 6, so we may want to keep those rows that have null values. We can have them by using the following query<jupyter_code>%%sql
SELECT order_id, freight FROM orders
WHERE freight is null
or freight <= 6
limit 5<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.<jupyter_text>The above query works, but we have a more elegant way of handling null values. We can use the **coalesce() function**- coalesce(col_name,replacement_value): It takes a column name that may have null value, if the row value is null,  then replace it with the given replacement_value.Below query use coalesce to replace all null value of column freight by 0 than compare them with <=6. This will not modify the origin table.<jupyter_code>%%sql
SELECT order_id, freight FROM orders
WHERE coalesce(freight,0) <= 6
limit 5<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.<jupyter_text>If we want the other user can use the replaced value, we can create a new column by using below query<jupyter_code>%%sql
SELECT order_id, coalesce(freight, 0) as freight_without_null
FROM orders
limit 5;<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.<jupyter_text>2.5 Grouping ConditionsWhen you start chaining "AND" and "OR" together, you need to make sure that you organize each set of conditions between each OR in a way that groups related conditions.For example, we need to find all orders that shipped by company 2 and freight is not bigger than 6 or the . We could write below query.<jupyter_code>%%sql
select * from orders 
where ship_via = 2 and freight <=6
or ship_city = 'Paris'
limit 5;<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.<jupyter_text>We are lucky this works, because AND, OR have the same priority, sql resolve from left to right. So **ship_via = 2 and freight <=6** resolved to a value, then this value get resolved with **or ship_city = 'Paris'**. But for a begginer, this can be confusing, he will wonder if AND condition get resolved first or the OR condition get resolved first.To avoid this, we can explicitly group conditions in parentheses. This makes not only makes the semantics clearer, but also the execution safer.<jupyter_code>%%sql
select * from orders 
where (ship_via = 2 and freight <=6)
or ship_city = 'Paris'
limit 5;<jupyter_output>* postgresql://user-pengfei:***@postgresql-124499/test
5 rows affected.
{'fraction_non_alphanumeric': 0.0849319190551418, 'fraction_numerical': 0.03287444519672008, 'mean_word_length': 5.098165137614679, 'pattern_counts': {'":': 0, '<': 89, '<?xml version=': 0, '>': 80, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 14, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '136807', 'license': 'Apache-2.0', 'n_tokens_mistral': 4207, 'n_tokens_neox': 3866, 'n_words': 1750, 'path': 'Insee/notebooks/02.Filtering_data.ipynb', 'repo_name': 'pengfei99/LearningSQL', 'size': '13293'}