Mastering Relational Database Queries
Advanced Selection Filtering
Retrieving Your Data
You've designed a well-structured database with normalized tables and clear relationships. Now it's time to pull specific information out of it. The primary tool for this is the SELECT statement. In its simplest form, you tell the database which columns you want to see and which table they're in.
SELECT first_name, last_name, email
FROM customers;
This query grabs the first_name, last_name, and email for every single entry in the customers table. But usually, you don't want everything. You need to filter the results to find exactly what you're looking for. That's where the WHERE clause comes in. It's the part of the query that applies your conditions.
The two most common and fundamental SQL statements are SELECT to retrieve certain fields from a set, and WHERE to filter the set based on certain criteria.
Applying Conditional Logic
The WHERE clause lets you set rules for the data you want to retrieve. These rules are built using comparison and logical operators. Comparison operators do exactly what their name implies: they compare values.
| Operator | Meaning |
|---|---|
| = | Equal to |
| != or <> | Not equal to |
| < | Less than |
| > | Greater than |
| <= | Less than or equal to |
| >= | Greater than or equal to |
For example, to find all products that cost more than $100, you would write:
SELECT product_name, price
FROM products
WHERE price > 100;
Things get more interesting when you combine conditions using logical operators: AND, OR, and NOT. These allow you to build complex logic to pinpoint data with precision.
AND requires all conditions to be true.
OR requires at least one condition to be true.
NOT negates a condition, finding rows where it's false.
Let's say you need to find all active customers in California or all customers who signed up before 2023, regardless of their status.
SELECT first_name, last_name, signup_date
FROM customers
WHERE (state = 'CA' AND status = 'active')
OR signup_date < '2023-01-01';
Use parentheses to group your
ANDandORconditions. This removes any ambiguity about the order of operations and makes your queries much easier to read and debug.
Searching for Patterns
Sometimes you don't know the exact value you're looking for, but you know a part of it. The LIKE operator is perfect for this kind of pattern matching. It uses special wildcard characters to stand in for unknown parts of a string.
| Wildcard | Description |
|---|---|
| % | Represents zero, one, or multiple characters |
| _ | Represents a single character |
If you wanted to find all employees with a last name starting with 'S', you could use:
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'S%';
The % wildcard here means "any sequence of characters." To find all people whose first name is 'Jon' or 'Jen', you could use the _ wildcard:
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'J_n';
Ranges and Lists
Two other powerful operators for filtering are BETWEEN and IN. They provide a much cleaner syntax than writing out long chains of comparisons.
The BETWEEN operator checks if a value falls within a specified range. This range is inclusive, meaning it includes the start and end values.
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2024-06-01' AND '2024-06-30';
The IN operator checks if a value matches any value in a list. This is a great way to avoid multiple OR conditions.
Instead of writing WHERE country = 'USA' OR country = 'Canada' OR country = 'Mexico', you can simplify it to:
SELECT customer_name, country
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');
Using IN makes the query more readable and often more efficient for the database to execute.
What is the primary function of the WHERE clause in a SQL SELECT statement?
You need to find all orders from a table named sales that were placed in January 2024 and have a total value over $50. Which query correctly retrieves this data?
Mastering these filtering techniques is fundamental. They allow you to translate complex business questions into precise queries that extract exactly the data you need from vast datasets.