Filtering Data with WHERE

Introduction

In the last lesson, you learned to retrieve data with SELECT. But what if you only want specific rows? The WHERE clause is your answer—it filters rows based on conditions you specify.

Think of WHERE as asking: "Of all the data in this table, which rows match my criteria?"

The WHERE Clause

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Your First WHERE Query

SELECT * FROM users WHERE age > 30;

Output:

 id |    name    |      email      | age
----+------------+-----------------+-----
  2 | Bob Jones  | bob@example.com |  34
  5 | Eve Davis  | eve@example.com |  34

Only rows where age > 30 are returned.

Comparison Operators

Numeric Comparisons

-- Equal to
SELECT * FROM users WHERE age = 28;

-- Not equal to
SELECT * FROM users WHERE age != 28;
SELECT * FROM users WHERE age <> 28;  -- Same as !=

-- Greater than
SELECT * FROM users WHERE age > 25;

-- Less than
SELECT * FROM users WHERE age < 30;

-- Greater than or equal to
SELECT * FROM users WHERE age >= 28;

-- Less than or equal to
SELECT * FROM users WHERE age <= 30;

String Comparisons

-- Exact match
SELECT * FROM users WHERE name = 'Alice Smith';

-- Case-sensitive comparison
SELECT * FROM users WHERE email = 'alice@example.com';

Important: Strings are case-sensitive!

-- Won't match
SELECT * FROM users WHERE name = 'alice smith';

-- Will match
SELECT * FROM users WHERE name = 'Alice Smith';

Logical Operators

AND - All Conditions Must Be True

SELECT * FROM users
WHERE age > 25 AND age < 35;

Output:

 id |    name     |        email         | age
----+-------------+----------------------+-----
  1 | Alice Smith | alice@example.com    |  28
  2 | Bob Jones   | bob@example.com      |  34
  4 | Dave Brown  | dave@example.com     |  28
  5 | Eve Davis   | eve@example.com      |  34

Only rows where both conditions are true.

OR - At Least One Condition Must Be True

SELECT * FROM users
WHERE age = 22 OR age = 34;

Output:

 id |    name     |      email       | age
----+-------------+------------------+-----
  2 | Bob Jones   | bob@example.com  |  34
  3 | Carol White | carol@example.com|  22
  5 | Eve Davis   | eve@example.com  |  34

Rows where either condition is true.

NOT - Negates a Condition

SELECT * FROM users
WHERE NOT age = 28;

-- Equivalent to:
SELECT * FROM users
WHERE age != 28;

Combining Logical Operators

Order of Operations

Use parentheses to control logic:

-- Get users who are either:
-- - Named Alice, OR
-- - Older than 30 AND named Bob

SELECT * FROM users
WHERE name = 'Alice Smith'
   OR (age > 30 AND name = 'Bob Jones');

Without parentheses (different result):

SELECT * FROM users
WHERE name = 'Alice Smith' OR age > 30 AND name = 'Bob Jones';
-- AND has higher precedence, so this is:
-- WHERE name = 'Alice Smith' OR (age > 30 AND name = 'Bob Jones')

Best practice: Always use parentheses for clarity!

BETWEEN - Range Queries

-- Ages between 25 and 30 (inclusive)
SELECT * FROM users
WHERE age BETWEEN 25 AND 30;

-- Equivalent to:
SELECT * FROM users
WHERE age >= 25 AND age <= 30;

BETWEEN is inclusive (includes both endpoints).

IN - Multiple Values

-- Get users with specific ages
SELECT * FROM users
WHERE age IN (22, 28, 34);

-- Equivalent to:
SELECT * FROM users
WHERE age = 22 OR age = 28 OR age = 34;

Much cleaner with IN!

NOT IN

SELECT * FROM users
WHERE age NOT IN (22, 28);

Gets users whose age is neither 22 nor 28.

LIKE - Pattern Matching

Wildcard Characters

• % matches any number of characters (including zero)
• _ matches exactly one character

Examples

-- Names starting with "A"
SELECT * FROM users WHERE name LIKE 'A%';

-- Names ending with "Smith"
SELECT * FROM users WHERE name LIKE '%Smith';

-- Names containing "ob"
SELECT * FROM users WHERE name LIKE '%ob%';

-- Email addresses from example.com
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Names with exactly 9 characters
SELECT * FROM users WHERE name LIKE '_________';  -- 9 underscores

ILIKE - Case-Insensitive LIKE (PostgreSQL)

-- Will match "alice", "Alice", "ALICE"
SELECT * FROM users WHERE name ILIKE 'alice%';

NOT LIKE

-- Names that don't start with "A"
SELECT * FROM users WHERE name NOT LIKE 'A%';

NULL Values

What is NULL?

NULL represents missing or unknown data—it's not zero, not an empty string, but the absence of a value.

Testing for NULL

-- ❌ WRONG - doesn't work!
SELECT * FROM users WHERE email = NULL;

-- ✅ CORRECT
SELECT * FROM users WHERE email IS NULL;

-- Find non-NULL values
SELECT * FROM users WHERE email IS NOT NULL;

Why = NULL doesn't work:

NULL means "unknown". You can't compare something to "unknown" using =.

Example with NULL

-- Add a user without an email
INSERT INTO users (name, age)
VALUES ('Frank Miller', 45);

-- Find users without emails
SELECT * FROM users WHERE email IS NULL;

Output:

 id |     name     | email | age
----+--------------+-------+-----
  6 | Frank Miller |       |  45

Practical Examples

Example 1: Find Young Adults

SELECT name, age
FROM users
WHERE age BETWEEN 18 AND 30
ORDER BY age;

Example 2: Find Gmail Users

SELECT name, email
FROM users
WHERE email LIKE '%@gmail.com';

Example 3: Complex Filter

-- Find users who are:
-- - Either under 25 years old
-- - Or have a name starting with "A"
-- - But NOT from gmail.com

SELECT *
FROM users
WHERE (age < 25 OR name LIKE 'A%')
  AND email NOT LIKE '%@gmail.com';

Performance Tips

Use Indexes for Common Filters

If you frequently filter by a column, add an index:

-- Create index on age column
CREATE INDEX idx_users_age ON users(age);

-- Now queries like this are faster:
SELECT * FROM users WHERE age = 28;

(We'll cover indexes in detail in Module 7)

Avoid Functions on Indexed Columns

-- ❌ Slow (can't use index)
SELECT * FROM users WHERE UPPER(name) = 'ALICE SMITH';

-- ✅ Faster (can use index)
SELECT * FROM users WHERE name = 'Alice Smith';

Common Mistakes

Mistake 1: Using = with NULL

-- ❌ Wrong
WHERE email = NULL

-- ✅ Correct
WHERE email IS NULL

Mistake 2: Forgetting Quotes for Strings

-- ❌ Wrong
WHERE name = Alice Smith

-- ✅ Correct
WHERE name = 'Alice Smith'

Mistake 3: Confusing AND/OR

-- Want: Users aged 25-30
-- ❌ Wrong (no results!)
WHERE age >= 25 OR age <= 30

-- ✅ Correct
WHERE age >= 25 AND age <= 30

Mistake 4: Case Sensitivity

-- Won't match "Alice Smith"
WHERE name = 'alice smith'

-- Use ILIKE for case-insensitive
WHERE name ILIKE 'alice smith'

Practice Exercises

Using your users table, write queries to:

Exercise 1

Find all users younger than 30.

SELECT * FROM users WHERE age < 30;

Exercise 2

Find users with names starting with "B" or "C".

SELECT * FROM users
WHERE name LIKE 'B%' OR name LIKE 'C%';

Exercise 3

Find users between ages 25 and 35 (inclusive).

SELECT * FROM users WHERE age BETWEEN 25 AND 35;

Exercise 4

Find users whose email contains "example.com".

SELECT * FROM users WHERE email LIKE '%example.com%';

Exercise 5

Find users who are NOT 28 or 34 years old.

SELECT * FROM users WHERE age NOT IN (28, 34);

Key Takeaways

• ✅ WHERE filters rows based on conditions
• ✅ Comparison operators: =, !=, <, >, <=, >=
• ✅ Logical operators: AND, OR, NOT
• ✅ BETWEEN: Range queries (inclusive)
• ✅ IN: Match multiple values
• ✅ LIKE: Pattern matching with % and _
• ✅ IS NULL / IS NOT NULL: Test for NULL values

Next Steps

You can now filter data precisely with WHERE! But what if you want to control the order of results? In the next lesson, we'll learn ORDER BY—how to sort your query results.

Let's keep building your SQL skills!