Filtering Results in SQL: A Comprehensive Guide

Welcome to our SQL tutorial series! In this installment, we will delve into filtering results in SQL using the WHERE clause. We will also cover the keywords BETWEEN and IN, enabling you to filter your data more effectively.

To keep things simple, we’ll use a straightforward table with six columns and five rows, generated on the fly, so no need for any installations. You can follow along with the examples provided, and all the code will be available in a GitHub repository along with an SQL cheat sheet.

Generating the Data

Here's a quick script to generate the necessary data using a CTE:

This query shall be appended to all following queries.

Filtering with the WHERE Clause

The WHERE clause is used to filter records based on specific conditions. Let's look at some examples.

Basic Filtering

Suppose we want to find employees with a salary of at least $80,000:

Filtering by Exact Value

If we want to find employees with a salary exactly equal to $80,000, we modify the query slightly:

Using BETWEEN

To filter employees with salaries between $60,000 and $80,000:

Filtering Text Columns

To filter by text columns, such as finding all employees named 'John':

Using Wildcards with LIKE

To search for names starting with 'B':

To find names ending with 'n':

Filtering with IN

To find employees named 'Ben' or 'Norman':

Handling NULL Values

Filtering NULL values requires special handling. For instance, to find employees without a manager:

Attempting to use = to filter NULL values will not work, as NULL represents an unknown state rather than a value.

Conclusion

Filtering data in SQL using the WHERE clause and keywords like BETWEEN and IN can greatly enhance your ability to query specific datasets. By practicing these techniques, you will become more proficient in writing efficient and effective SQL queries.

Happy querying!