Filtering multiple criteria

1. Filtering multiple criteria

Single filters are helpful, but in exploration and analysis, we often need to filter for multiple conditions at once.

2. Filtering multiple criteria

Take our book search. We may be looking for a sci-fi book released in 2010, or sci-fi or fantasy book, or a book between 200 and 300 pages. These multi-layered conditions are common in real-world analysis, so let's look at how we can apply them to our film database.

3. All requirements are true

We'll start with combining requirements that must all be true. We want a short, English-language film that came out after 1990. The prompt "Show English films under 90 minutes released after 1990" uses AND to require all three conditions, and every condition must match.

4. Either OR

Let's mix it up. We're interested in films released after 1990 that are either English or French. Let's try it with the prompt, "Show English or French language films released after 1990". The result is a WHERE clause query with AND and OR operators. The AND captures films released after 1990, while OR allows films in either language. Notice that the language field is stated twice. We also have parentheses around the language conditions.

5. Parentheses

Without parentheses, the results would show films released after 1990 in French, or English films from any year. This is a great example of applying our SQL skills and verifying that the output is what we intended. The parentheses ensure the filter correctly returns English or French language films from the intended release year.

6. Data ranges

For ranges, BETWEEN streamlines our filtering. The prompt "Show films released between 1990 and 1999" generates a query equivalent to using the greater than or less than operators.

7. Checking multiple values

Our multiple criteria list may get long. Let's narrow down the results to films from Japan, Spain, or Italy. From what we know so far, the result would string together several OR operators, which would still be correct.

8. Checking multiple values

However, AI assistants like to take an efficient approach, which in this case would be using IN and listing the criteria in one go.

9. Excluding multiple values

This works the same way if we want to exclude a set of results, showing all films except those in English or French. This time, the query shows a NOT IN operator to filter out the unwanted values.

10. Verify complex filters

The AI interprets our intent well, but we should verify complex filtering combinations to ensure the result contains the information we wanted. To summarize what we've learned, use AND to require multiple conditions, OR to accept alternatives, BETWEEN for ranges, and IN/NOT IN for multiple specific values. Remember, parentheses group conditions to control logic; without them, our filters might break and include unintended results.

11. Let's practice!

Next, we'll explore pattern matching for even more flexible text filtering. Let's practice combining filter criteria!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.