Filtering: Beginner

Narrow results to only matching rows

WHERE narrows your result set to only the rows that match a condition. Without it, every query returns the entire table. When a table holds millions of rows, the difference between a full scan and a targeted filter determines whether a query is useful at all.

The WHERE keyword is your entry point to filtering. It tells SQL exactly which rows you want to keep based on conditions you define.

WHERE is the word you use to start filtering. It comes after FROM and tells SQL what condition to check.

Everything after WHERE is your filter condition. The condition is a test that can be true or false for each row.

Problem: Find all students with a grade of A. Solution: Use WHERE with the = operator to keep only rows where grade is exactly "A."

The equals sign (=) checks for exact matches. It is the most common filter you will use.

When you write status = 'active', SQL keeps only rows where status is exactly "active". Everything else gets filtered out.

Problem: Show only active devices. Solution: Filter for status = 'active'. Devices with "inactive", "pending", or any other status are excluded.

Use = when you know exactly what you are looking for:

Row-by-row check

The database examines each row individually against your condition.

Exact match only

Keeps rows where the values match exactly, nothing approximate.

Works with all types

Compares text, numbers, and dates with the same syntax.

Practice using WHERE and the equals operator to filter products by category.

SELECT
  name,
  price
FROM products
___ category ___ 'electronics'

The WHERE clause is the foundation of almost every analytical query. Without it, you retrieve the entire table, which is rarely what you need.

Exact matching with the = operator is the most efficient type of filter because the database can use indexes to locate matching rows directly.

As your queries grow more complex, you will combine WHERE with multiple conditions, but the single-condition WHERE clause you practiced here remains the building block for all of them.

Match or exclude rows by exact values

The != operator means "not equal to". It is the opposite of =. Use it to exclude one specific value.

When you write status != 'inactive', you keep everything EXCEPT rows where status is "inactive". Watch how the filter evaluates each row against this condition.

Rows with "inactive" are filtered out (device_002 and device_004). Active and pending rows pass through because those values are not equal to "inactive."

Problem: Show all devices except inactive ones. Solution: Use != to exclude "inactive". You get active, pending, and maintenance devices.

By filtering out trial accounts with !=, we get a clean list of users who have converted to paid plans.

Both != and <> mean exactly the same thing. Think of <> as a visual representation of "not equal" -- the angle brackets point away from each other, suggesting inequality.

Most programmers prefer != because it is more common in other programming languages, but <> is the traditional SQL standard. Use whichever feels more natural to you.

SELECT
  order_id,
  account_type
FROM orders
WHERE account_type ___ ___

The <> operator is the SQL standard for not-equals. Use it whenever you need to exclude a single specific value from your results.

Not equals filters are most effective when you want to exclude a single known bad value from a dataset, such as removing test accounts or internal records before analysis.

Find rows above or below a threshold

Problem: Find big spenders who spent more than $500. Solution: Use > 500. Users who spent exactly $500 are NOT included. Only amounts above 500 pass the filter.

Problem: Find products low on stock (10 or fewer items). With <=: Products with exactly 10 items ARE included. With <: Products with exactly 10 items would be EXCLUDED.

Problem: Find orders of $200 or less. Solution: Use <= to include the boundary value. Orders at exactly $200 are included because the operator is "less than or equal to."

SELECT
  product_id,
  ___
FROM products
WHERE price ___ 50

In the next section, you will learn to combine comparison operators to create powerful range filters for date-based reporting and numeric threshold analysis.

Match rows against a list of values

IN checks if a value matches anything in a list. Instead of writing multiple = checks with OR, you can list all acceptable values in one place.

Pattern for IN operator:

Problem: Show only books, tools, and toys. Solution: List all three types in IN. Notice that "food" is excluded because it is not in the list.

Watch how IN checks each row against the list of allowed values.

Without IN, you would need to write a separate = check for each value. IN replaces all of those with a single, clean expression.

This single IN expression checks against three values at once. Adding more values just means extending the list.

The IN operator simplifies checking if a value matches any item in a list.

List membership check

Checks if the value appears anywhere in your provided list.

Cleaner than OR chains

Replaces many OR conditions with a single readable expression.

Works with all types

Accepts both text strings and numbers in the value list.

Just as IN finds rows matching a list, NOT IN excludes rows matching a list. This is invaluable for filtering out unwanted categories.

The NOT IN operator is the inverse of IN. It returns rows where a value does not match any item in a specified list. This is useful when you need to exclude multiple specific values at once.

While you could chain multiple != conditions together, NOT IN provides a much cleaner and more maintainable syntax when excluding several values.

NOT IN is especially valuable in analytics and reporting when you need to exclude test data, invalid records, or specific categories:

Compare IN and NOT IN on the same data:

Knowing when to use IN versus NOT IN simplifies multi-value filtering.

SELECT
  order_id,
  ___
FROM orders
WHERE status ___ (
  'Shipped',
  'Delivered',
  'Returned'
  )

IN is equivalent to chaining multiple OR conditions but produces cleaner, more maintainable SQL.

NOT IN excludes rows matching any value in the list, useful for filtering out unwanted categories or statuses.

Be cautious with NOT IN when the list might contain NULL values, as this can cause unexpected empty results.

Combine multiple filters in one query

Real-world queries often need multiple criteria. The AND operator lets you layer conditions to narrow results precisely.

AND lets you combine multiple conditions. A row must pass ALL conditions to be included. If any condition fails, the row is excluded.

Think of AND as stacking filters on top of each other. Each filter narrows down the results further.

Problem: Find active US users who logged in recently. Solution: Use AND to require all three conditions. Users must be from US AND be active AND have logged in after January 1st.

AND shines when you need to create precise segments from your data by combining multiple requirements.

AND is essential whenever you need to narrow results with multiple criteria.

Most real queries combine several operators. Here is an example using =, >, and IN all at once:

Problem: Find large completed orders from North America. Solution: Combine three filters. Orders must be completed AND over $100 AND from either US or Canada.