Working with Single-Table Data

Filtering with WHERE

---

Once a query can return rows from a table, the next step is often to ask for only some of them.

For example:

• What deadlines do exercises called SQL Basics have?
• Which submissions have a score of at least 5?
• Which exercises are due in March?

SQL provides ways to filter results so that a query returns only the rows that match the question. Filtering is done with the WHERE clause, which is added after the FROM clause.

A First Filter

Suppose the question is: “What deadlines do exercises called SQL Basics have?”

SELECT deadline_at
FROM exercises
WHERE title = 'SQL Basics';

This query returns the deadline_at values from the rows whose title is exactly SQL Basics.

This is a useful reminder of the roles of the clauses:

• WHERE decides which rows stay in the result
• SELECT decides which values are shown from those rows

Without the WHERE clause, the query would return the deadlines of every exercise.

It helps to read the condition in words: keep the rows where the title is SQL Basics. Reading out the queries is a good habit to practice early. It helps you check that the condition matches the question and that you understand what the query does before you run it.

Loading Exercise...

Common Comparison Operators

Different questions lead to different kinds of comparisons. You will often use operators such as:

• = equal to
• <> not equal to
• < less than
• > greater than
• <= less than or equal to
• >= greater than or equal to

For example, if the question is “Which exercises are due on or after 1 March 2026?”, one natural comparison is:

SELECT
  id,
  title,
  deadline_at
FROM exercises
WHERE deadline_at >= '2026-03-01';

This keeps only rows whose deadline_at value is on or after 1 March 2026.

The main idea is to choose an operator that fits the question you are asking. If the question asks for an exact text value, = often fits. If the question asks for before, after, smaller, or larger, then comparison operators such as <, >, <=, or >= are often a better fit.

The column type also affects how a filter is written.

• Numeric columns are often filtered with comparisons such as score >= 5
• Text columns are often filtered with checks such as title = 'SQL Basics'
• Date or timestamp columns are often filtered with before/after or range conditions such as deadline_at >= '2026-03-01'

The values in the condition should make sense for the type of the column. Write strings and timestamps in single quotes. Write numbers without quotes. Double quotes are used for identifiers, not for string values.

Loading Exercise...

Combining Conditions

Many useful questions need more than one condition. Conditions can be combined with AND, OR, and NOT.

SELECT
  id,
  title,
  deadline_at
FROM exercises
WHERE deadline_at >= '2026-03-01'
  AND deadline_at < '2026-04-01';

Suppose the question is: “Which exercises have a deadline in March 2026?”

This query answers that by combining two smaller ideas:

• the deadline is on or after 1 March
• the deadline is before 1 April

This is often easier to read than trying to compress everything into one line.

As a practical rule:

• AND makes the result narrower
• OR often makes the result wider

That is not the whole theory, but it is a helpful intuition.

If one condition means “deadline is in March” and another means “title is SQL Basics”, then:

• condition_a AND condition_b keeps only rows that satisfy both
• condition_a OR condition_b keeps rows that satisfy either one or both

This is one of the first places where SQL often feels conceptually harder. The condition may still look short, but changing one word can change the meaning a lot. Then again, if you can implement the condition in some programming language, you can usually write it in SQL as well. The main difference is that SQL has its own syntax and operators, but the logic of combining conditions is the same.

As conditions grow longer, parentheses often help readability.

For example:

SELECT
  id,
  title
FROM exercises
WHERE (deadline_at >= '2026-03-01' AND deadline_at < '2026-04-01')
   OR title = 'Practice';

Even when parentheses are not strictly required, they can make the intended logic much easier to see.

A good habit is to say the condition in words before trusting the result. If you cannot explain what the condition means, it is usually worth rewriting it more clearly.

Loading Exercise...

Common Filter Patterns

Some filtering questions appear so often that SQL provides shorter, more natural patterns for them. Useful examples include IN, BETWEEN, and LIKE.

If the question is “Which submissions have one of these known statuses?”, IN is often clearer than repeating several equality checks:

SELECT
  id,
  status,
  score
FROM submissions
WHERE status IN ('submitted', 'graded');

If the question is “Which submissions have a score in this range?”, BETWEEN can express that directly:

SELECT
  id,
  score
FROM submissions
WHERE score BETWEEN 5 AND 10;

Loading Exercise...

In many systems, users also want search-like filtering. In SQL, LIKE is used for text values when you want to match part of a string rather than the whole value exactly.

If the question is “Which exercise titles contain the text SQL?”, one simple answer is:

SELECT
  id,
  title
FROM exercises
WHERE title LIKE '%SQL%';

The percent sign acts like a wildcard. It means “any sequence of characters”.

PostgreSQL also has an ILIKE operator that works like LIKE but ignores case. That is, title ILIKE '%sql%' would match titles that contain SQL, sql, Sql, etc.

Loading Exercise...

Check Your Understanding

1. What is the difference between combining conditions with AND and with OR?
2. Why are parentheses sometimes important in WHERE clauses?
3. Why is it useful to test a complicated filter on a small dataset first?

SQL Practice

Complete the SQL sequence below to practice writing filters with WHERE and combining conditions clearly.

Loading Exercise...