Back to articles list July 18, 2018 - 3 minutes read SQL Filtering 101 Aldo Zelen Aldo is a data architect with a passion for the cloud. From leading a team of data professionals to coding a data warehouse in the cloud, Aldo has experience with the whole lifecycle of data-intensive projects. Aldo spends his free time forecasting geopolitical events in forecasting tournaments. Tags: sql basics sql how to how to in sql 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! Tags: sql basics sql how to how to in sql You may also like Why Take the “SQL Basics” Course at LearnSQL.com Do you work with data? Do you want to learn the foundations of SQL? Discover why you should take our interactive online course “SQL Basics”! Read more SQL INSERT, SQL UPDATE, SQL DELETE – Oh My! Need to understand SQL INSERT, UPDATE, or DELETE? We'll get you up to speed with a comprehensive How-To and useful examples! Read more SQL Indexing 101 Indexes are one of the most misused and misunderstood entities in physical database design. In this article, we'll look at basic database indexes and their role in database development. Read more New LearnSQL.com Online Practice – Solve Basic SQL Questions with the SQL Practice Set We created our SQL practice set to help you master your SQL skills. Get access to our SQL online practice exercises for free! Read more How to Sort Records with the ORDER BY Clause We'll take a closer look at the ORDER BY clause in SQL – how to write it, how it works, and what it does to a relational database. Read more Oracle Top-N Queries for Absolute Beginners Top-N queries retrieve a defined number of rows from a result set, i.e. 10 best-selling cars, 5 most popular routers, 20 worst-performing stores, etc. Read more The Complete Beginner’s Guide to SQL Fundamentals Find out what SQL and databases are, and how they work together. In this article, you'll find a complete guide to SQL fundamentals. Read more Refine Results with SQL Set Operators The function of SQL set operators is pretty simple; they allow us to combine results from different SQL queries into one result set. Read more How to Use a SQL Wildcard SQL wildcard allows us to filter data matching certain patterns. In this article, we’ll look at everything you need to know about basic SQL wildcards. Read more Our Picks for 2020’s 7 Best Online SQL Schools Looking for the best online SQL courses but aren’t sure where to start? I evaluated the top 7 ranked online SQL schools to help you find your match. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.