5 Ways To Filter Data With WHERE in SQL

Across the last few tutorials, we've shown you multiple ways to filter data. To review, we covered

  • how allows us to filter rows based on specified conditions
  • how allow you to combine multiple filtering conditions
  • how allows you to filter on a range of values
  • how allows you to specify a list of values that you'd like to filter on
  • how allows you to match a value against a pattern

Here's a comprehensive table of the different operators you can use in conjunction with to filter your data:

OperatorDefinitionExample in QueryInterpretation
=Equals touser_id is equal to 2
!=, <>Not equals touser_id is not equal to 5
<, >Less than, more thanage is less than 5
<=, >=Less than or equal to, more than or equal toage is equal to or greater than 2
BETWEEN … AND …Integer is within range of two values (inclusive)pushups is between 20 and 40 (inclusive)
IN (…)Value is in a listfirst name is either Bob, Ann, or Joe
LIKEChecks if a string matches a patternfirst name starts with "Jo"

SQL Filtering Data Practice Exercise

It's time to practice your filtering ability by analyzing a dataset of 1,000 customer records from a small-business in Australia.

Here's a sample of the input data you're given:

Sample Input:
1Ignace WhillockMale305464JohnstonhavenNorthern Territory
2Gray EskrickFemale698223New ZacharyfortSouth Australia
3Ellswerth LaurentMale595661AliburghAustralian Capital Territory

Write a SQL query to find all customers who are between the ages of 18 and 22 (inclusive), live in either Victoria, Tasmania, Queensland, their gender isn't "n/a", and their name starts with either 'A' or 'B'.

Your output would look something like this:

123Aidan GoselingFemale221149JordanboroughTasmania
402Adeline PerringtonFemale221712West CooperTasmania
692Bertrando MelonbyMale204058LaraviewTasmania
900Audrie FrottonFemale229455East EmilyTasmania

Be warned – this exercise will need you to combine multiple SQL keywords in order to arrive at the solution!

What's Next: ORDER BY

In the practice exercise above where we filtered customer record data, we got the data in order of user_id, from lowest to highest.

But what if we wanted to make it in order of their age? And how could we handle ties?

SQL Order By Customer Records Example

That's where the next lesson comes in handy: ORDER BY Tutorial!

Next Lesson