Advanced Data Retrieval

Refining Your Data Hunt

You already know how to pull data from a table using SELECT. Now, let's get surgical. The real power of SQL isn't just grabbing data, but grabbing the exact data you need. This means mastering the WHERE clause with more advanced filtering techniques.

Combining Conditions

Often, a single condition isn't enough. You might need to find customers who live in California and signed up after a certain date. Or maybe you want products that are either discounted or low in stock. For this, you use logical operators: AND, OR, and NOT.

AND requires all conditions to be true. OR requires at least one condition to be true. NOT inverts a condition.

Let's find all employees in the 'Sales' department who were hired after January 1, 2022.

SELECT FirstName, LastName, HireDate
FROM Employees
WHERE Department = 'Sales' AND HireDate > '2022-01-01';

Now, what if we want employees who are either in the 'Sales' department or have a salary over $75,000? We use OR.

SELECT FirstName, LastName, Department, Salary
FROM Employees
WHERE Department = 'Sales' OR Salary > 75000;

When you combine operators, SQL has a specific order of operations. This is called —it evaluates NOT first, then AND, and finally OR. If you need to override this default order, just use parentheses () to group your conditions, just like in mathematics.

For example, to find employees who are not in the 'Sales' department but are either managers or have a salary over $90,000:

SELECT FirstName, LastName, Department, Title, Salary
FROM Employees
WHERE NOT Department = 'Sales'
  AND (Title = 'Manager' OR Salary > 90000);

Finding Patterns

What if you don't know the exact value you're looking for? Maybe you're searching for all customers whose last name starts with 'S', or all products with 'USB' somewhere in their name. The LIKE operator is perfect for this kind of pattern matching in text fields.

LIKE works with special characters called wildcards.

Wildcard	Description
`%`	Matches any sequence of zero or more characters.
`_`	Matches any single character.

To find all customers whose last name starts with 'Sm':

SELECT FirstName, LastName
FROM Customers
WHERE LastName LIKE 'Sm%';

The % matches everything that comes after 'Sm'. To find a five-letter product name where the second letter is 'a' and the fourth is 'e', you could use underscores:

SELECT ProductName
FROM Products
WHERE ProductName LIKE '_a_e_';

Working with Blanks

Sometimes, a field in a database is empty. It doesn't contain a zero or a blank space; it contains a special marker called that signifies an unknown or missing value.

A common mistake is trying to filter these with standard operators. For example, WHERE MiddleName = NULL will not work as expected. You can't say something is "equal to" an unknown. Instead, SQL gives us a special syntax for this.

Use IS NULL to find rows where a value is missing, and IS NOT NULL to find rows where a value is present.

SELECT FirstName, LastName
FROM Employees
WHERE ReportsTo IS NULL;

This query would likely find the CEO or the highest-ranking person in the table, as they don't report to anyone.

Smarter Filtering

SQL provides a couple of shorthand operators that make your queries cleaner and often more readable than stringing together multiple AND or OR conditions.

The BETWEEN operator lets you select values within a range. It's inclusive, meaning it includes the start and end values.

SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 50 AND 100;

This is simply a cleaner way of writing WHERE Price >= 50 AND Price <= 100. It works for numbers, text, and dates.

The IN operator lets you specify a list of values to match. It's a tidy alternative to a long chain of OR conditions.

SELECT * 
FROM Customers
WHERE Country IN ('USA', 'Canada', 'Mexico');

This is much easier to read than WHERE Country = 'USA' OR Country = 'Canada' OR Country = 'Mexico'.

Finally, as queries get more complex, you can use aliases to assign temporary, more readable names to columns or tables. An is created with the AS keyword (though in many SQL dialects, AS is optional).

SELECT 
    FirstName AS First,
    LastName AS Last,
    Salary * 0.15 AS Bonus
FROM Employees;

Here, we've renamed FirstName to First, LastName to Last, and given a calculated column the clear name Bonus.

Quiz Questions 1/6

Consider the following SQL query: SELECT * FROM Employees WHERE Department = 'Marketing' OR Department = 'Sales' AND Salary > 80000;. Which employees will this query select?

Quiz Questions 2/6

Which WHERE clause correctly finds all products with a five-character name where the second letter is 'o' and the last letter is 'e'?

These tools give you precise control to slice and dice your data, ensuring you get exactly the information you need.

Mastering SQL for Data Analysis