Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 125 ยท Querying Data

Filtering with WHERE

Filter rows with the SQL WHERE clause. Use comparison operators on numbers, text and dates, quote values correctly, and avoid the classic date and type traps.

Real tables are big. A shop has thousands of orders, a company has hundreds of employees, and you almost never want all of them at once. The WHERE clause is how you say which rows you want: only delivered orders, only employees hired this year, only products under 20 euros.

WHERE is also the clause that matters most for correctness. The same condition that filters a SELECT decides which rows an UPDATE changes or a DELETE removes. In this lesson you'll learn how WHERE works, every comparison operator, and the subtle ways text, numbers and dates behave when you compare them. The examples use the company and shop databases.

How WHERE works

WHERE comes right after FROM and holds a condition: an expression that is true or false for each row.

SQL
SELECT columns
FROM table_name
WHERE condition;

The database looks at every row, evaluates the condition, and keeps only the rows where it is true. Rows where it is false, or unknown (more on that soon), are dropped before the select list is computed.

SQL Employees in one department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Department 2 is Design, and the condition department_id = 2 is true for four employees. The column in the condition doesn't need to appear in the select list; here it doesn't.

Comparison operators

These six operators are the building blocks of nearly every filter:

Operator Meaning Example
= Equal to status = 'shipped'
<> Not equal to (standard) status <> 'cancelled'
!= Not equal to (widely supported alternative) status != 'cancelled'
< Less than price < 20
> Greater than salary > 100000
<= Less than or equal to stock <= 5
>= Greater than or equal to hire_date >= '2024-01-01'

SQL uses a single = for comparison, not == as in many programming languages. (SQLite accepts == as well, but no other major database does, so stick to =.) Every database supports both <> and !=; <> is the one in the standard.

SQL Comparing numbers ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Four people earn at least 140,000. Change >= to > or the number to 146000 and watch the list change. Pay attention to the boundary: with >=, an employee earning exactly 146,000 is included; with >, they aren't. Off-by-one mistakes at boundaries are among the most common filtering bugs.

The not-equal operator is handy for excluding one value:

SQL Excluding a value ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You'll combine several conditions with AND, OR and NOT in the next lesson, and match lists and ranges with IN and BETWEEN.

Comparing text

Text values go in single quotes. Without quotes, SQL reads a word as a column name:

SQL Text needs quotes ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Remove the quotes around Support Lead and the run fails with Error: near "Lead": syntax error. With a one-word value the message is more telling: WHERE title = Contractor fails with Error: no such column: Contractor, because SQL thinks you meant a column. The syntax lesson explains why double quotes are no substitute.

Text comparison in SQLite is exact: case matters, and so do spaces. 'support lead' and 'Support Lead ' (with a trailing space) both fail to match. To match regardless of case, compare lowercase versions:

SQL Case-insensitive matching ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

As you saw in SQL Dialects and SQLite, MySQL and SQL Server usually compare text case-insensitively by default, so the same query without lower() would also work there. Writing lower() (or using LIKE, covered in Pattern Matching with LIKE) makes your intent explicit and portable.

Ordering text

< and > work on text too. They compare alphabetically, character by character, using each character's code. For plain English letters that means:

  • 'Apple' < 'Banana' is true: A comes before B.
  • 'Zebra' < 'apple' is also true: every uppercase letter sorts before every lowercase one.
  • '10' < '9' is true for text: the first characters are compared, and 1 comes before 9.
SQL How text compares

Press Run (or Ctrl + Enter) to see the result here.

SQLite shows true as 1 and false as 0. The last two columns show why the type of a value matters: the text '10' is smaller than '9', but the number 10 is not smaller than 9. If numbers are ever stored as text, comparisons and sorting quietly go wrong. Store numbers in numeric columns.

Text comparisons are useful for alphabetical ranges, such as every last name at the start of the alphabet:

SQL An alphabetical range ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

last_name < 'C' keeps every name that sorts before the letter C: names starting with A and B. To include names starting with C, you'd write last_name < 'D'.

Numbers don't need quotes

Write numbers without quotes: salary > 100000, price <= 19.99. Don't put thousands separators in them, so it's 100000, not 100,000 (a comma would separate two values) and not 100.000. Decimals always use a dot.

What if you quote a number by accident? Most databases convert it for you when the column is numeric, so salary > '100000' usually still works. In SQLite it works too, because the column's numeric type converts the text before comparing. But it's a bad habit: in other situations, such as comparing two text values, quoted numbers compare as text, with the '10' < '9' surprise you just saw.

Comparing dates

SQL has standard DATE and TIMESTAMP types, and PostgreSQL, MySQL and SQL Server use them. SQLite has no separate date type; it stores dates as text in ISO 8601 format: 'YYYY-MM-DD' for dates and 'YYYY-MM-DD HH:MM' or 'YYYY-MM-DD HH:MM:SS' for date-times.

That format was chosen carefully: because the biggest unit comes first and every part has a fixed width, alphabetical order is the same as chronological order. So ordinary comparison operators work on these strings:

SQL Recent hires ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The three most recent hires all joined in 2025. This works only when every date follows the same format. A date stored as '5/3/2025' or '2025-3-5' compares wrongly, which is one more reason to always store ISO dates. In databases with real date types, the same query works because the database converts '2025-01-01' into a date for you.

The time-of-day trap

The ordered_at column in the shop database holds a date and a time, like '2025-03-01 07:35'. Watch what happens when you look for orders on a single day:

SQL Equality on a date-time fails ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

No rows, even though orders were placed that day. The text '2025-03-01 07:35' is not equal to '2025-03-01'. There are two good fixes:

SQL Two ways to match a whole day ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both return the same two orders. The range version is usually better: it lets the database use an index on ordered_at, while wrapping the column in a function like date() usually prevents that. You'll see why in Writing Fast Queries. The date functions themselves are covered in Date and Time Functions.

Use half-open ranges for time periods. "Everything in March" is >= '2025-03-01' AND < '2025-04-01'. You never have to know how many days the month has, or worry about a time like 23:59:59.5 slipping past an <= '2025-03-31 23:59:59' boundary.

A first look at NULL

What about a missing value? In the company database, a few employees have no email address; the column holds NULL. You might expect email = NULL to find them:

SQL = NULL finds nothing ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns nothing; the second finds three employees. Comparing anything with NULL using =, <> or < gives neither true nor false but unknown, and WHERE only keeps rows where the condition is true. Always use IS NULL and IS NOT NULL. This "three-valued logic" has its own lesson, Working with NULL, because it affects almost every clause.

WHERE beyond SELECT

The WHERE clause works the same way in UPDATE and DELETE, where a mistake changes real data:

SQL
-- Changes one order
UPDATE orders
SET status = 'shipped'
WHERE id = 42;

-- Changes EVERY order: the WHERE clause is missing
UPDATE orders
SET status = 'shipped';

A safe workflow: write the WHERE clause in a SELECT first, check that it returns exactly the rows you mean, then reuse it in the UPDATE or DELETE. See UPDATE and DELETE for more.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which condition keeps employees earning exactly 90000 or more?

    >= includes the boundary value. => is not an SQL operator, and == tests equality only (and is SQLite-only).
  2. 2. What does SELECT '10' < '9'; return in SQLite?

    Both values are text, so they're compared alphabetically: '1' comes before '9', making the result true (1).
  3. 3. The column ordered_at holds values like '2025-03-01 07:35'. Which condition finds every order placed on March 1, 2025?

    A half-open range covers the whole day, whatever the time. Equality fails because the stored text includes a time.
  4. 4. Why does WHERE email = NULL return no rows?

    Any comparison with NULL using = is unknown, never true. Use IS NULL.
  5. 5. In SQLite, which condition matches the title 'Support Lead' when you aren't sure how it's capitalized?

    SQLite's = is case-sensitive for text, so compare lowercase versions. Double quotes would make an identifier.

Summary

  • WHERE keeps only the rows for which its condition is true; it runs before the select list.
  • The comparison operators are =, <> (or !=), <, >, <= and >=. Watch boundaries: > excludes the value, >= includes it.
  • Text goes in single quotes and compares exactly in SQLite (case and spaces matter); use lower() for case-insensitive matches.
  • Text compares character by character, so '10' < '9'; keep numbers in numeric columns and write them without quotes.
  • ISO dates ('YYYY-MM-DD') compare correctly as text. For date-times, use half-open ranges like >= '2025-03-01' AND < '2025-03-02'.
  • = NULL never matches; use IS NULL.

Next: AND, OR and NOT, where you'll combine conditions into precise filters.