Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 6 of 49

SQL WHERE: filtering rows

Filter rows with WHERE and the comparison operators for numbers, text and dates, and learn how the database decides which rows to keep.

12 minFiltering and sortingUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonFilter rows with WHERE using the comparison operators on numbers, text and dates, and predict which rows a condition keeps.

What you will be able to do

After this lesson you can ask for only the rows you care about: the people in one department, salaries above a limit, or staff hired after a date. WHERE is the clause that turns a table of everything into an answer to a specific question.

The basic form

Put WHERE after FROM, followed by a condition. The database tests the condition against every row and keeps the ones for which it is true:

sql
SELECT first_name, last_name, job_title
FROM employees
WHERE department_id = 1;
result
+------------+-----------+----------------------+
| first_name | last_name |      job_title       |
+------------+-----------+----------------------+
| Meera      | Iyer      | Engineering Director |
| Arjun      | Rao       | Senior Developer     |
| Kavya      | Nair      | Senior Developer     |
| Rohan      | Das       | Developer            |
| Isha       | Kapoor    | Developer            |
| Diya       | Chopra    | Junior Developer     |
+------------+-----------+----------------------+

Six employees belong to Engineering, which is department 1. The other fourteen rows were tested and dropped. The condition is department_id = 1: a column, a comparison operator and a value.

Comparison operators

Operator Meaning
= equal to
<> not equal to (!= also works on all four databases in this course)
< and > less than, greater than
<= and >= less than or equal, greater than or equal

Note that the equality test uses one equals sign. There is no == in SQL. Numbers need no quotes:

sql
SELECT first_name, salary
FROM employees
WHERE salary > 100000;
result
+------------+--------+
| first_name | salary |
+------------+--------+
| Meera      | 180000 |
| Arjun      | 125000 |
| Kavya      | 125000 |
| Vikram     | 150000 |
| Dev        | 110000 |
| Lata       | 115000 |
+------------+--------+

The rule > excludes the boundary value, so someone earning exactly 100000 would not appear. Use >= to include it.

Text

Text goes in single quotes, and the match must be exact, character for character apart from case rules, which differ by database (see below):

sql
SELECT first_name, last_name, salary
FROM employees
WHERE job_title = 'Support Engineer';
result
+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Karan      | Mehta     |  56000 |
| Sana       | Qureshi   |  56000 |
+------------+-----------+--------+

You can also compare text for ordering. Letters sort alphabetically, so last_name < 'D' finds names that come before D:

sql
SELECT first_name, last_name
FROM employees
WHERE last_name < 'D';
result
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Lata       | Bose      |
| Diya       | Chopra    |
+------------+-----------+

Bose and Chopra sort before 'D'. Das does not: it starts with D but is longer, and a longer text sorts after its own prefix, so 'Das' is greater than 'D'.

Dates

Dates are written as text in the form YYYY-MM-DD, in single quotes. This format sorts correctly and is understood by MySQL, PostgreSQL, SQLite and SQL Server:

sql
SELECT first_name, hire_date
FROM employees
WHERE hire_date >= '2023-01-01';
result
+------------+------------+
| first_name | hire_date  |
+------------+------------+
| Tara       | 2023-04-03 |
| Sana       | 2023-07-17 |
| Ravi       | 2024-01-08 |
| Diya       | 2024-06-10 |
| Aman       | 2024-03-18 |
+------------+------------+

Five people joined in 2023 or later. SQLite stores dates as plain text and compares them as text, which works because of the year-first layout. Other databases have real date types and compare them as dates. Avoid regional formats such as '01/02/2023', which are ambiguous and not portable.

Not equal

Use <> to exclude a value:

sql
SELECT first_name, job_title
FROM employees
WHERE department_id <> 1 AND salary >= 110000;
result
+------------+-------------------+
| first_name |     job_title     |
+------------+-------------------+
| Vikram     | Sales Director    |
| Dev        | Marketing Manager |
| Lata       | Finance Manager   |
+------------+-------------------+

The AND joining two conditions is the subject of the next lesson. Notice that only rows with both conditions true appear. The consultant, who has no department, is absent for a reason you will meet in the NULL lesson: department_id <> 1 is unknown, not true, when the department is missing.

Calculations in a condition

Either side of a comparison can be a calculation. Which projects would still have a budget above 150000 if it were cut in half?

sql
SELECT name, budget
FROM projects
WHERE budget / 2 > 150000;
result
+-----------------+--------+
|      name       | budget |
+-----------------+--------+
| Customer Portal | 400000 |
| Mobile App      | 650000 |
+-----------------+--------+

A condition can also compare two columns of the same row. You will use that when tables are joined, because then the two columns often come from different tables.

How WHERE decides

For each row, the condition evaluates to true, false or unknown. Only true keeps the row. Unknown appears when NULL is involved, and the result is the same as false for the purposes of WHERE. The NULL lesson covers this properly.

WHERE is evaluated before the select list, so it can use any column of the table, even one you do not show:

sql
SELECT first_name
FROM employees
WHERE salary = 125000;
result
+------------+
| first_name |
+------------+
| Arjun      |
| Kavya      |
+------------+

The salary is tested, but only the name is returned.

Differences between databases

  • Case in text comparisons. MySQL and SQL Server compare text without regard to case with their default settings, so 'developer' matches 'Developer'. PostgreSQL is case sensitive. SQLite is case sensitive for =. This depends on collation settings, so test before you rely on it.
  • Not equal. <> is standard and works everywhere. != is accepted by all four.
  • Dates. The YYYY-MM-DD form works on all four. Only SQLite stores dates as text.

See the MySQL WHERE lesson for more on that product.

Common mistakes

  • Using == instead of =.
  • Quoting numbers or forgetting to quote text. WHERE job_title = Developer looks for a column called Developer.
  • Double quotes for text. It sometimes works by accident on MySQL and SQLite and fails on PostgreSQL.
  • Using an alias from the select list in WHERE. It is not available there.
  • Expecting <> to keep rows with NULL. Missing values drop out of both = and <> tests.

Exercise

Find the employees in department 2 (Sales) who earn less than 60000, showing first_name, job_title and salary.

Show one possible answer
sql
SELECT first_name, job_title, salary
FROM employees
WHERE department_id = 2 AND salary < 60000;
result
+------------+-----------------+--------+
| first_name |    job_title    | salary |
+------------+-----------------+--------+
| Tara       | Sales Associate |  52000 |
| Aman       | Sales Associate |  50000 |
+------------+-----------------+--------+

Key points

  • WHERE keeps only the rows for which its condition is true. It runs before the select list is built.
  • The comparison operators are =, <>, <, >, <= and >=. Most databases also accept != for not equal.
  • Text and date values are written in single quotes. Dates use the YYYY-MM-DD format.
  • A single = tests equality in SQL. There is no == operator.
  • Rows where the condition is false or unknown (NULL) are left out of the result.

Check yourself

1. Which condition keeps employees in department 2?
2. Which writes a text comparison correctly?
3. Which operator means not equal in standard SQL?
4. A row has salary NULL. What does WHERE salary > 50000 do with it?