Learning SQL: WHERE

October 1, 2024

What is the WHERE Clause?

The WHERE clause is an essential SQL feature that allows you to filter records based on specific conditions. When you’re dealing with large datasets, you might not want all the data from a table. The WHERE clause helps you narrow down the result set, so you only get the records that meet certain criteria.

Basics of the WHERE Clause

The WHERE clause is used with the SELECT statement to specify which rows to retrieve. Here’s the basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

For example, if you want to retrieve all the customers from a customers table who live in New York, your query would look like this:

SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York';

This query will only return the customers whose city column equals 'New York'.

Operators You Can Use with WHERE

The WHERE clause supports a wide range of operators in defining conditions. Some common operators include:

= (equal to)
<> or != (not equal to)
> (greater than)
< (less than)
>= (greater than or equal to)
<= (less than or equal to)
BETWEEN (within a range)
IN (match any value in a list)
LIKE (pattern matching)
IS NULL (check for NULL values)

Here are a few examples:

Retrieve customers who are older than 30:

SELECT first_name, last_name, age
FROM customers
WHERE age > 30;

Retrieve customers from specific cities:

SELECT first_name, last_name, city
FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago');

Retrieve orders placed within a specific date range:

SELECT order_id, order_date, total
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Retrieve customers whose first name starts with the letter “A” using the LIKE operator:

SELECT first_name, last_name
FROM customers
WHERE first_name LIKE 'A%';

Here, % is a wildcard matching any characters after “A”, returning names like “Alice” or “Andrew.”

Combining Conditions with AND & OR

You can combine multiple conditions using the AND and OR operators:

AND: Both conditions must be true.
OR: At least one condition must be true.

For example, to retrieve customers who live in New York and are older than 30:

SELECT first_name, last_name, city, age
FROM customers
WHERE city = 'New York' AND age > 30;

Or to retrieve customers who live in either New York or Los Angeles:

SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York' OR city = 'Los Angeles';

Recap

The WHERE clause allows you to filter records and retrieve only the data that meets specific criteria. By using different operators like =, IN, LIKE, and combining conditions with AND and OR, you can create powerful and precise queries that improve the efficiency of your data retrieval process.

Resources

Thank you

Thank you for your time and for reading this!