💡 Ask Tutor

Chapter 7: Filtering Data with the WHERE Clause

In the previous chapter, you mastered the SELECT statement to retrieve data from your tables. However, simply getting “all users” or “all products” is rarely enough for a real-world web application. You’ll often need to narrow down your results based on specific conditions – for example, finding a user by their ID, showing products within a certain price range, or listing only active blog posts.

This is where the WHERE clause comes into play. The WHERE clause allows you to specify criteria that each row must meet to be included in the result set. It’s an indispensable tool for targeted data retrieval.

The Purpose of WHERE

The WHERE clause is an optional but frequently used part of the SELECT statement (and also UPDATE and DELETE statements, as we’ll see later). It filters the rows based on a specified condition. Only rows for which the condition evaluates to TRUE are included in the result.

Basic Structure:

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

  • condition: An expression that evaluates to TRUE, FALSE, or UNKNOWN (for NULL values).

Operators Used in WHERE Clause

To build your conditions, you’ll use various operators:

Comparison Operators: Used to compare two values.

  • = : Equal to
  • != or <> : Not equal to
  • > : Greater than
  • < : Less than
  • >= : Greater than or equal to
  • <= : Less than or equal to

Logical Operators: Used to combine multiple conditions.

  • AND: Returns TRUE if all conditions are TRUE.
  • OR: Returns TRUE if any of the conditions are TRUE.
  • NOT: Negates a condition (returns TRUE if the condition is FALSE).

Special Operators:

  • IN (value1, value2, ...): Matches any value in a list.
  • BETWEEN value1 AND value2: Matches values within a specified range (inclusive).
  • LIKE pattern: Matches values against a pattern (often used with wildcards).
  • IS NULL: Tests for NULL values.
  • IS NOT NULL: Tests for non-NULL values.

SQL Syntax & Examples

Let’s use our scriptbuzz_db and the data we’ve inserted. Remember to run USE scriptbuzz_db; first.

1. Using Comparison Operators

Example 1: Select a specific user by user_id

SQL
SELECT * FROM users
WHERE user_id = 1;

Expected Output (example): Only the row for ‘john_doe’ (user_id 1) will be returned.

user_idusernameemailpasswordis_admincreated_atlast_login
1john_doejohn.doe@example.comhashed_password_102025-06-29 21:00:00NULL

Example 2: Select products with price greater than $100

SQL
SELECT name, price FROM products
WHERE price > 100.00;

Expected Output (example):

nameprice
Laptop Pro1200.00

Example 3: Select products not in stock (stock_quantity is 0)

SQL
SELECT name, stock_quantity FROM products
WHERE stock_quantity = 0;

Expected Output (example):

namestock_quantity
Webcam 1080p0

2. Using Logical Operators (AND, OR, NOT)

Example 4: Select products that are active AND have stock greater than 50

SQL
SELECT name, stock_quantity, is_active FROM products
WHERE is_active = 1 AND stock_quantity > 50;

Expected Output (example):

namestock_quantityis_active
Wireless Mouse2001
USB-C Hub1001

Example 5: Select users who are admins OR have ‘jane.doe’ in their email

SQL
SELECT username, email, is_admin FROM users
WHERE is_admin = 1 OR email = 'jane.doe@example.com';

Expected Output (example):

usernameemailis_admin
admin_useradmin@example.com1
jane_doejane.doe@example.com0

Example 6: Select products that are NOT active

SQL
SELECT name, is_active FROM products
WHERE NOT is_active = 1;
-- Alternatively: WHERE is_active = 0;

Expected Output (example):

nameis_active
Webcam 1080p0

Parentheses for Clarity: Use parentheses () to group conditions, especially with AND and OR, to control the order of evaluation and prevent ambiguity.

SQL
SELECT username, email FROM users
WHERE (is_admin = 1 AND created_at > '2025-01-01') OR username = 'john_doe';

This query first evaluates the AND condition, then combines that result with the OR condition.

3. Using Special Operators (IN, BETWEEN, LIKE, IS NULL)

Example 7: IN – Select users with specific usernames

SQL
SELECT * FROM users
WHERE username IN ('john_doe', 'admin_user');

What it does: Returns rows where username is either ‘john_doe’ OR ‘admin_user’. This is cleaner than using multiple OR conditions.

Expected Output (example):

user_idusernameemailpasswordis_admincreated_atlast_login
1john_doejohn.doe@example.comhashed_password_102025-06-29 21:00:00NULL
2admin_useradmin@example.comhashed_password_admin12025-06-29 21:05:00NULL

Example 8: BETWEEN – Select products with price between $50 and $100 (inclusive)

SQL
SELECT name, price FROM products
WHERE price BETWEEN 50.00 AND 100.00;

What it does: Returns products where the price is greater than or equal to 50.00 AND less than or equal to 100.00.

Expected Output (example):

nameprice
Mechanical Keyboard99.99
Webcam 1080p65.00

Example 9: LIKE – Select products whose name starts with ‘W’ The LIKE operator is used for pattern matching.

  • %: Represents zero, one, or multiple characters.
  • _: Represents a single character.
SQL
SELECT name FROM products
WHERE name LIKE 'W%';

SQL
SELECT name FROM products
WHERE name LIKE 'W%';

What it does: Returns products where the name starts with the letter ‘W’.

Expected Output (example):

name
Wireless Mouse
Webcam 1080p

Example 10: LIKE – Select products with ‘Mouse’ anywhere in the name

SQL
SELECT name FROM products
WHERE name LIKE '%Mouse%';

Expected Output (example):

name
Wireless Mouse

Example 11: IS NULL – Select users who have not logged in yet

SQL
SELECT username, last_login FROM users
WHERE last_login IS NULL;

What it does: Returns users where the last_login column has a NULL value. Note that last_login = NULL does not work; you must use IS NULL.

Expected Output (example):

usernamelast_login
john_doeNULL
admin_userNULL
jane_doeNULL

Example 12: IS NOT NULL – Select products that have a description

SQL
SELECT name, description FROM products
WHERE description IS NOT NULL;

Expected Output (example):

namedescription
Laptop ProPowerful laptop for professionals.
Mech KeyboardRGB backlit, tactile switches.
USB-C HubMulti-port adapter for laptops.

Common Pitfalls and Tricky Aspects

  • NULL vs. Empty String: NULL means “no value” or “unknown.” An empty string ('') is a known value (an empty string). WHERE column = NULL will almost always return nothing. You must use IS NULL or IS NOT NULL.
  • Case Sensitivity with LIKE: In MySQL/MariaDB, LIKE comparisons are generally case-insensitive by default for non-binary string types, but this can depend on the column’s collation. If you need strict case-sensitive matching, you might need to use COLLATE or BINARY keyword, but for basic filtering, it’s usually not an issue.
  • Date/Time Comparisons: When comparing dates and times, ensure your input format matches the database’s expectation (YYYY-MM-DD HH:MM:SS). You can compare dates using comparison operators (>, <, =, etc.).

Notes:

  • The WHERE clause filters rows based on a specified condition.
  • Comparison Operators (=, !=, >, <, >=, <=) are used for direct value comparisons.
  • Logical Operators (AND, OR, NOT) combine multiple conditions. Use parentheses () for clarity and to control evaluation order.
  • Special Operators:
    • IN: For matching against a list of values.
    • BETWEEN: For matching values within an inclusive range.
    • LIKE: For pattern matching using wildcards (% for multiple chars, _ for single char).
    • IS NULL / IS NOT NULL: For checking if a column contains NULL values.
  • Always use IS NULL instead of = NULL when checking for NULL values.

Small Exercise

Using your scriptbuzz_db:

  1. Select all users whose username starts with ‘j’.
  2. Select products that have a price less than 50.00 OR a stock_quantity of 0.
  3. If you have an orders table, select all orders that were placed after a specific date (e.g., ‘2025-06-15’).
  4. If you have a posts table, select all unpublished posts (is_published or similar flag is 0 or FALSE) that have NULL in their content column (if applicable, or pick another nullable column you defined).

You’re now truly in control of what data you see from your database! Next, we’ll learn how to arrange that data in a meaningful order.

📘 Continue to the Next Chapter

Sorting Data with ORDER BY

Discover how to sort query results using the ORDER BY clause in SQL for better data presentation and logic.

➡️

🚀 Explore Popular Learning Tracks