G. Filtering Data

One of the most powerful feature of using SQL is the ability to rapidly filter data to match a certain criteria you are searching for.

1. WHERE

By using the WHERE filters with a query it allows us to only return results that match a set condition. We can use this together with conditional operators like =, >, <, >=, <=, etc.

1SELECT *
2FROM airbnb_listings 
3WHERE price < 300;

2. AND

We can combine two or more conditions into a single query by using the AND command. All of the conditions must be met for the result to be returned. Let’s say we want results for listings over $300 in Brooklyn.

1SELECT name
2FROM airbnb_listings
3WHERE price < 300 AND neighbourhood_group = 'Brooklyn';

3. OR

We can combine two or more conditions into a single query by using the OR command. It differs from AND in the sense that only one of the conditions must be met for a result to be returned. Therefore from our example all listings priced over $300 will be returned as well as all listings located in Brooklyn.

1SELECT name
2FROM airbnb_listings
3WHERE price < 300 OR neighbourhood_group = 'Brooklyn';

4. BETWEEN

We can specify a range using the BETWEEN filter. For example creating a price range for listings as follows.

1SELECT name, price
2FROM airbnb_listings
3WHERE price BETWEEN 200 AND 400; 

5. LIKE

We can search for patterns within our data using the LIKE command. Suppose you want to find listings with the word 'country' in the title, the code below will achieve this.

1SELECT name
2FROM airbnb_listings
3WHERE name LIKE ‘%country%’;

Other operators for LIKE:

• %x — will select all values that begin with x
• %x% — will select all values that include x
• x% — will select all values that end with x
• x%y — will select all values that begin with x and end with y
• _x% — will select all values have x as the second character
• x% — will select all values that begin with x and are at least two characters long. You can add additional characters to extend the length requirement, i.e. x___%

6. IN

By using the IN command we can specify multiple values we want to select from with the WHERE command.

1SELECT name, neighbourhood_group, price
2FROM airbnb_listings
3WHERE neighbourhood_group IN ('Brooklyn', 'Manhattan');

7. IS NULL & IS NOT NULL

IS NULL will return only rows with a NULL value, while IS NOT NULL does the opposite returning only rows without a NULL value. Let’s say we want to find listings which have received no reviews in the past month.

1-- IS NULL
2SELECT name
3FROM airbnb_listings
4WHERE reviews_per_month IS NULL;
5
6-- IS NOT NULL
7SELECT name
8FROM airbnb_listings
9WHERE reviews_per_month IS NOT NULL;