SQL Filtering 101

Sometimes, SQL queries return lots of data you don't need. In this article, we'll look at a simple example of filtering SQL queries to reduce the complexity of your data.

The key to learning how to filter data in SQL is understanding the very basics of Boolean algebra. That's just a fancy term mathematicians use to describe expressions involving logical values (true/false) and their associated operators (and, or, not, etc.). Don't be discouraged by this jargon; filtering is just common sense formalized by mathematics.

Let's look at a simple customer table:

This table contains all the customers of our fictional pet store. We list the name, surname, city of residency, country of residence, date of birth, and pet type for each customer who has visited our store.

Of course, we're not always interested in looking at each and every customer in the table. We may be interested in filtering the table to show data for all customers who, for example, happen to live in Berlin. We'd filter the data using a simple expression in the WHERE clause of our query:

SELECT * FROM customers WHERE city = 'Berlin';

Here, we're applying the filter condition with the equality operator (=), which you can read aloud as "IS". In other words, the above query tells the database to select all data for customers whose city is Berlin. Pretty simple, right?

Excluding records from result sets

How would you go about asking the inverse of the earlier question? That is, how would you find all customers except those who live in Berlin? We simply apply the exclusion operator (!) to the equality operator to get the composite inequality operator (!=). So we'd write:

SELECT * FROM customers WHERE city != 'Berlin';

Filtering dates

Now, let's look at filtering dates in SQL. Suppose we'd like to find all clients born after 1984-01-01. The equivalent logical expression should ask for a date greater than this constant. So we'd write the following query:

SELECT * FROM customers WHERE date_of_birth > '1984-01-01';

Naturally, we used the greater-than operator (>). If we'd would like to ask the inverse—that is, to retrieve data for all customers born on or before 1984-01-01, we would switch the sign:

SELECT * FROM customers WHERE date_of_birth <= '1984-01-01';

Compound conditions

What if we'd like to combine some conditions and find all customers who, for example, were born after 1984-01-01, own dogs, and are located in Berlin? We'd chain our filter conditions one after another (in no particular order) using the and operator. Here's the query:

SELECT * FROM customers WHERE date_of_birth > '1984-01-01' and pet ='Dog' and city = 'Berlin';

If we'd like to select all of clients who either reside in Berlin or have a dog, we'd simply swap the AND for an OR:

SELECT * FROM customers WHERE pet ='Dog' or city = 'Berlin';

Want to learn more?

In this simple tutorial, you learned how to filter data in SQL using Boolean operators and conditions. Of course, this was just an intro—applying filters is a skill you need to master to become proficient in SQL. To gain more hands-on experience with filtering tables in SQL, be sure to check out this section of our SQL Basics course. And remember: practice is the key to mastering SQL!