Filter rows without losing the unknowns

Estimated study time: 60–75 minutes. Read, predict, run, explain, then attempt the independent work.

The manager narrows the request to completed orders in February. A WHERE clause keeps rows that satisfy a condition. It operates on individual input rows before grouping. Equality uses =; inequality can use <>; comparisons also include <, <=, > and >=. Text values use single quotes, such as 'completed'. An unquoted word is interpreted as an identifier, not a text label.

The February condition combines three requirements with AND: the order is completed, the date is at least 1 February, and the date is before 1 March. All must be true. This half-open interval includes the start and excludes the next period's start. It is a reusable reporting pattern because adjacent periods do not overlap. With timestamps, it also avoids assuming that a day ends at 23:59:59. Our dates are plain calendar dates; a real timestamp report additionally needs an agreed time zone.

OR means either condition can be true. Mixing AND and OR without parentheses is a common way to admit unintended rows. SQL evaluates AND more tightly than OR, so make the intended groups visible. The Boolean example requires a completed order and then permits either an app order or customer 1. Without the parentheses, an OR condition at the end can bypass an earlier completion requirement.

Missing data has a third truth state

NULL is not the text 'NULL', an empty string, or zero. It means the value is missing or unknown. A comparison such as city = NULL produces unknown, not true. WHERE keeps only true rows, so that comparison will not find Farah. Use IS NULL to ask whether a value is absent and IS NOT NULL to ask whether it is present.

The same rule explains a quieter error: city <> 'Pune' does not include customers whose city is unknown. You cannot infer that a missing city is elsewhere. If the question explicitly means “not known to be Pune, including unknown cities,” write the two cases using OR city IS NULL. The words in the question decide whether that is appropriate.

COALESCE chooses the first non-null argument. In a display column, COALESCE(city,'Unknown') gives a useful label without changing the stored field. It does not establish that an unknown city is a genuine geographical category. Keep the original null semantics in mind when calculating completeness rates or joining to location data. Do not silently replace missing money with zero unless the source and metric make zero a valid interpretation.

Build a filter you can explain

Start with the smallest condition and inspect its output. Then add the date range, then any extra channel rule. Check records exactly on the boundaries. In this fixture 1005 is on 1 February, 1008 is on 28 February, and 1009 is on 1 March. A correct February filter should retain the first two and exclude the third. Checking only a middle-of-month order would miss the boundary defect.

Before using a filter in a larger report, write a plain-English inclusion rule and identify a counterexample. A cancelled app order is a useful counterexample to a misplaced OR. A missing city is a useful counterexample to an ordinary comparison. These small, deliberate tests are more informative than asking an AI tool whether the query looks correct.

Worked example 03 filter

The typed DATE literals make the intended data type explicit. The ::text cast affects the displayed date format for our runner; it does not change the filter.

SELECT order_id, customer_id, order_date::text AS order_date
FROM aabha_sql.orders
WHERE status = 'completed'
  AND order_date >= DATE '2025-02-01'
  AND order_date < DATE '2025-03-01'
ORDER BY order_date, order_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/03_filter.sql.

Worked example 03 nulls

The filter uses IS NULL on the source value. COALESCE is only used to label the returned result.

SELECT customer_id, customer_name, COALESCE(city,'Unknown') AS city_label
FROM aabha_sql.customers WHERE city IS NULL ORDER BY customer_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/03_nulls.sql.

Worked example 03 logic

Check that cancelled app order 1013 is absent. It satisfies the app condition but fails the completed condition.

SELECT order_id, status, channel FROM aabha_sql.orders
WHERE status = 'completed' AND (channel = 'app' OR customer_id = 1)
ORDER BY order_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/03_logic.sql.

Independent practice

03a

Return order_id and status for noncompleted orders in February 2025, ordered by order_id.

Hint: Apply the same half-open date range and change the status condition.

03b

Return customer_id and customer_name for customers in Pune or with a missing city, ordered by customer_id.

Hint: Unknown city is an explicit second case, not an inequality.

Check before moving on

You can predict whether an unknown comparison is retained by WHERE, and explain why February excludes order 1009 even though it is completed.