This chapter covers intricate WHERE conditions, pattern matching, range filtering, and null checking, providing clear examples and explanations. Learn to leverage SQL's powerful capabilities for managing and analyzing data.
Improve your readability in:
SQL 101 | Chapter 5: Advanced SQL Filtering - How to Refine Your Queries for Better Data Insights
This chapter covers intricate WHERE conditions, pattern matching, range filtering, and null checking, providing clear examples and explanations. Learn to leverage SQL's powerful capabilities for managing and analyzing data.
blog.ardenov.com
Introduction Advanced filtering techniques allow you to refine your queries with greater precision, enabling you to pinpoint specific data points based on complex conditions. This chapter delves into several sophisticated methods for filtering your data using SQL. From handling intricate WHERE conditions to mastering pattern matching, range filtering, and null checking, these techniques provide a robust toolkit for managing and analyzing data. Each method will be explored with clear examples and explanations, illustrating how to leverage SQL’s powerful capabilities to meet diverse data needs. To illustrate these concepts, we’ll use the tables from Chapter 3. Let’s dive into these advanced filtering techniques, starting with how to handle complex WHERE conditions. Complex WHERE Conditions Complex WHERE conditions in SQL enable you to refine your query results by combining multiple criteria. These conditions leverage logical operators such as AND, OR, and NOT to filter records based on various attributes. Below, we explore several practical examples to demonstrate these techniques. Syntax Format: To apply complex WHERE conditions, use the following syntax format: SELECT column_1 FROM table_1 WHERE condition_1 AND/OR/NOT condition_2; This format allows you to combine multiple conditions to retrieve precisely the data you need. Example 1: Books with more than 300 pages and authored by someone with popularity above 3 In this example, we aim to find books that are not only lengthy but also written by highly popular authors. We achieve this by joining the books and authors tables and applying conditions on both the number of pages and the author’s popularity. SELECT b.book_title , b.total_pages , a.popularity FROM books b JOIN authors a ON b.author_id = a.author_id WHERE b.total_pages >= 300 AND a.popularity > 3; This query joins the books and authors tables on the author_id column. It then filters for books with minimum 300 pages and only includes those written by authors with a popularity score greater than 3. This ensures that both criteria are met for the results to be included. Books with minimum 300 pages and popular authors Books with minimum 300 pages and popular authors Example 2: Books that are either in the ‘Biography’ genre or have maximum 300 pages This query targets books that fit into one of two categories: those belonging to the ‘Biography’ genre or those with a maximum 300 page count. SELECT book_title , book_genre , total_pages FROM books WHERE book_genre = 'Biography' OR total_pages <= 300; By using the OR operator, this query retrieves books that either fall under the ‘Biography’ genre or have maximum 300 pages. The OR operator broadens the search criteria to include more diverse results. Books in ‘Biography’ genre or maximum 300 pages Books in ‘Biography’ genre or maximum 300 pages Example 3: Books Not in the ‘Fiction’ and ‘Biography’ genre and having fewer than 800 pages Here, we exclude books from the ‘Fiction’ and ‘Biography’ genre and focus on those with fewer than 800 pages. SELECT book_title , book_genre , total_pages FROM books WHERE (NOT book_genre IN ('Fiction', 'Biography')) AND total_pages < 800; This query uses the NOT operator to exclude books categorized as ‘Fiction’ and ‘Biography’ and applies an additional condition to select books with fewer than 800 pages. Combining NOT with AND allows for precise filtering of the dataset. Books not in ‘Fiction’ and ‘Biography’ genre and fewer than 800 pages Books not in ‘Fiction’ and ‘Biography’ genre and fewer than 800 pages Pattern Matching with LIKE The LIKE operator in SQL is a powerful tool for searching text fields based on specific patterns. It allows you to filter records by matching patterns using wildcard characters. This section demonstrates various ways to use LIKE for pattern matching. Syntax Format: To perform pattern matching, use the following syntax format: SELECT column_1 FROM table_1 WHERE column_1 LIKE 'pattern'; Here, 'pattern' represents the search pattern with wildcards to match various parts of the text. SQL wildcards are used in LIKE clauses to search for a specified pattern in a column. Here are the most common SQL wildcards: Percent (%): Represents zero, one, or multiple characters. Example: WHERE name LIKE 'A%' finds any values that start with “A”. Example: WHERE name LIKE '%abc%' finds any values that contain “abc”. Underscore (): Represents a single character. Example: WHERE name LIKE 'A' finds any values that have “A” as the first character and any single character as the second. Example: WHERE name LIKE 'n' finds any values where the second character is “n”. Example 1: Finding authors whose names start with “A” SELECT author_name FROM authors WHERE author_name LIKE 'A%'; This retrieves the names of authors from the authors table whose names start with the letter ‘A’. The LIKE 'A%' condition is used to match any author_name that begins with ‘A’, followed by any sequence of characters. Authors whose names start with “A” Authors whose names start with “A” Example 2: Finding authors whose names have “i” as the second character SELECT author_name FROM authors WHERE author_name LIKE '_i%'; The query retrieves the names of authors from the authors table where the name contains the letter “i” as the second character. The underscore () in the LIKE '_i%' condition acts as a wildcard for any single character, and % matches any sequence of characters following “i”. Authors whose names have “i” as the second character Authors whose names have “i” as the second character Example 3: Finding authors whose phone numbers ending in “567” SELECT author_name , phone FROM authors WHERE phone LIKE '%567'; This selects the names and phone numbers of authors from the authors table where the phone number ends with “567”. The % symbol is used as a wildcard to match any sequence of characters before “567”. Authors whose phone numbers ending in “567” Authors whose phone numbers ending in “567” Example 4: Finding authors with “pine” in address SELECT author_name , address FROM authors WHERE LOWER(address) LIKE '%pine%'; This query retrieves the names and addresses of authors from the authors table where the address contains the word “pine” (case-insensitive). The LOWER function is used to ensure that the search is not affected by letter case, and %pine% matches any address containing the substring “pine”. Authors with “pine” in address Authors with “pine” in address Range Filtering with BETWEEN The BETWEEN operator in SQL is a powerful tool for filtering records within a specific range. It allows you to specify a range of values to select records that fall between the given limits. This section explores various applications of range filtering using BETWEEN. Syntax Format: To filter records within a range, use the following syntax format: SELECT column_1 FROM table_1 WHERE column_1 BETWEEN value_1 AND value_2; Here, value_1 and value_2 define the inclusive range for filtering records. Example 1: Sales records with a sale date between January 1, 2022, and January 31, 2022 This query retrieves sales records that fall within a specified date range. This is useful for analyzing sales data within a particular timeframe. SELECT * FROM sales WHERE date_sale BETWEEN '2022-01-01' AND '2022-01-31'; The BETWEEN operator is used to select sales records where the date_sale falls between January 1, 2022, and January 31, 2022. This allows you to focus on sales data within the month of January 2022. Sales records with a sale date between January 1, 2022, and January 31, 2022 Sales records with a sale date between January 1, 2022, and January 31, 2022 Example 2: Sales with prices ranging from $120 to $140 Here, we filter sales records to find those with prices within a specified range. This helps in analyzing sales within a certain price bracket. SELECT * FROM sales WHERE price BETWEEN 120 AND 140; The BETWEEN operator filters sales records where the price is between $120 and $140. This range helps in evaluating sales within a particular price range. Sales with prices ranging from $120 to $140 Sales with prices ranging from $120 to $140 Null Checking with IS NULL and IS NOT NULL Handling null values is crucial in SQL to manage and analyze incomplete or missing data. The IS NULL and IS NOT NULL operators are used to filter records based on the presence or absence of data in specific columns. Syntax Format for IS NULL To find records where a column value is null, use the following syntax: SELECT column_1 FROM table_1 WHERE column_1 IS NULL; This query retrieves rows where the specified column contains null values, indicating the absence of data. Syntax Format for IS NOT NULL To find records where a column value is not null, use the following syntax: SELECT column_1 FROM table_1 WHERE column_1 IS NOT NULL; This query retrieves rows where the specified column contains non-null values, indicating the presence of data. Example 1: Find authors who don’t have books This query retrieves the names of authors who don’t have any books associated with them. SELECT a.author_name , b.book_id FROM authors a LEFT JOIN books b USING(author_id) WHERE b.book_id IS NULL It performs a left join between the authors and books tables on author_id and filters to show only those authors where book_id is NULL, indicating they don’t have any books listed. Authors who don’t have books Authors who don’t have books Example 2: List books with sales information: This query fetches the titles of books along with their corresponding sale IDs and sale dates. SELECT b.book_title , s.sale_id , s.date_sale FROM books b LEFT JOIN sales s USING(book_id) WHERE s.sale_id IS NOT NULL It performs a left join between the books and sales tables using book_id, and filters the results to include only those entries where sale_id is not NULL, indicating that the book has been sold. Books with sales information Books with sales information Conclusion By applying these advanced filtering techniques, you can effectively manage and analyze your data, gaining deeper insights and making more informed decisions. Whether you’re dealing with large datasets or complex queries, mastering these methods will enhance your SQL skills and productivity.
Originally published at https://blog.ardenov.com.
Top comments (0)