Filtering and Sorting with Purpose

Huda Saleh, VP Marketing, writes: “Can you pull the orders that used SPRING15 or WELCOME10 on our own channels, web or the app? I want to see whether the welcome offer pulls people into the app.” Her sentence contains two ORs and an AND. That’s the shape of request that produces silently wrong numbers, so it’s worth getting WHERE exactly right.

AND before OR

Here’s the first draft most people type, translating her words left to right:

SELECT COUNT(*) AS orders
FROM orders
WHERE channel = 'web' OR channel = 'mobile_app'
  AND coupon_code IN ('SPRING15', 'WELCOME10');

1,191 orders. That’s more than half of all orders in two years, for two niche coupons. The number is the alarm: AND binds tighter than OR, so SQL read this as “every web order, plus app orders that used a coupon”. Every web order is in there, coupon or not.

Parentheses say what Huda meant:

SELECT COUNT(*) AS orders
FROM orders
WHERE (channel = 'web' OR channel = 'mobile_app')
  AND coupon_code IN ('SPRING15', 'WELCOME10');

110 orders. Better still, the channel test can use IN too, which removes the OR entirely: channel IN ('web', 'mobile_app'). IN checks one column against a list, and a list can’t be mis-grouped.

The rest of the filtering toolkit

A few operators cover nearly every filter you’ll write:

Text comparison has a SQLite-specific twist. = is case-sensitive, so 'a' = 'A' is false, while LIKE ignores case for ASCII letters, so 'a' LIKE 'A' is true. That’s why name LIKE '%mat%' finds both “Desk Mat XL” and “Cork Yoga Mat”. PostgreSQL’s LIKE is case-sensitive, a difference you’ll meet again in the dialects lesson.

SELECT product_id, name, unit_price
FROM products
WHERE name LIKE '%mat%'
  AND unit_price BETWEEN 20 AND 80;

Saying “not” correctly

Negation is where careful people slip. Suppose Huda now wants orders that are neither cancelled nor returned. You can write it two ways, and they’re equivalent:

SELECT
  (SELECT COUNT(*) FROM orders
   WHERE NOT (status = 'cancelled' OR status = 'returned')) AS not_either,
  (SELECT COUNT(*) FROM orders
   WHERE status <> 'cancelled' AND status <> 'returned') AS both_not;

Both return 1,864. The rule behind it is De Morgan’s law: pushing a NOT inside the parentheses flips every OR into an AND (and every AND into an OR). The common bug is to flip the comparisons but forget to flip the connector: status <> 'cancelled' OR status <> 'returned' is TRUE for every row, because any status differs from at least one of the two. When you write a negative filter, NOT IN ('cancelled', 'returned') is the clearest form of all.

One more habit pays off here. Before trusting a filter with several conditions, count rows for each condition on its own. Huda’s filter has three parts: web or app keeps 1,848 rows, the two coupons keep 127, and “not cancelled or returned” keeps 1,864. Their AND can’t keep more than the smallest part, 127; if it does, the logic is wrong. A ten-second check like that catches most logic errors before anyone else sees them.

Dates in Cartwheel are ISO text (2025-11-28 19:55:30), which sorts in the same order as time. So order_date >= '2025-11-01' works as a date filter. The next lessons cover the traps, starting with what happens at the end of a day.

Sorting you can rely on

ORDER BY takes several keys, each with its own direction. The second key only matters among rows that tie on the first:

SELECT order_id, customer_id, order_date, coupon_code
FROM orders
WHERE channel = 'marketplace'
  AND coupon_code IS NOT NULL
ORDER BY order_date DESC, order_id
LIMIT 5;

Why add order_id when dates rarely tie? Because “rarely” isn’t “never”. When two rows tie on every sort key, SQL makes no promise about their order, and it can differ between runs. Ending the ORDER BY with a unique column, usually the primary key, makes the order fully deterministic.

That matters most for paging. LIMIT 20 OFFSET 40 means “skip 40 rows, return the next 20”, the third page of 20. If the order isn’t deterministic, a row can show up on page two and again on page three.

Huda’s coupon question is answered: 110 orders. Her follow-up is in the exercise below. Next comes the value that breaks all of these operators in surprising ways: NULL.