Where clause and operators: in, between, like, not in, comparison operators, wildcard, order by, group by, distinct

📖 Theory

Do not give me everything

SELECT * FROM sales dumps every row. But Meera rarely wants everything, she wants 'groceries under 50', or 'items starting with S', or 'the list sorted by price'.

That selectivity is the WHERE clause and its companions. This is where SELECT stops being a data dump and becomes a question.

And you have met the idea before: WHERE is exactly Unit 2's Filter and ORDER BY is exactly its Sort, now typed instead of clicked. Here is the shop table again to keep in view.

⌨️ Practical

The sales table (keep this in mind)

-- id | item  | category | price | qty
--  1 | Sugar | Grocery  |   45  | 20
--  2 | Tea   | Beverage |  120  | 15
--  3 | Rice  | Grocery  |   60  | 30
--  4 | Soap  | Personal |   35  | 25
--  5 | Milk  | Dairy    |   30  | 40

SELECT item, price FROM sales
WHERE category = 'Grocery' AND price < 50;
-- returns: Sugar 45

This example runs in Gri-Learn on the web, where you can edit it and see the output.

📊 At a glance

WHERE operators

📖 Theory

LIKE and the wildcards

LIKE matches text patterns using two wildcards:

• `%` stands for any sequence of characters (including none).
• `_` stands for exactly one character.

So item LIKE 'S%' matches Sugar and Soap (start with S). item LIKE '%a' matches anything ending in 'a'. item LIKE 'T__' matches 'Tea' (T + exactly two more).

This is how you search 'names starting with', 'containing', 'ending with', the pattern-matching workhorse of SQL. Getting % (many) vs _ (one) straight is a guaranteed mark.

📖 Theory

Shaping the result: ORDER BY, DISTINCT, GROUP BY

Beyond filtering rows, three clauses shape the output:

• ORDER BY sorts: ORDER BY price DESC (highest first; ASC is the default).
• DISTINCT removes duplicate values: SELECT DISTINCT category FROM sales lists each category once.
• GROUP BY collapses rows sharing a value into groups, used with aggregate functions for per-group summaries: SELECT category, SUM(qty) FROM sales GROUP BY category gives total quantity per category.

WHERE picks rows; GROUP BY bundles them; ORDER BY arranges them; DISTINCT dedupes them.

🧩 Quiz

Which items does WHERE item LIKE 'S%' match in the sales table?

1. Sugar and Soap (names starting with S)
2. Only Sugar (exact match)
3. All items containing the letter S
4. Tea and Rice

💭 Think first

BETWEEN is inclusive

Meera runs SELECT item FROM sales WHERE price BETWEEN 30 AND 60. Looking at the table (prices 45, 120, 60, 35, 30), which items come back? Watch the boundaries carefully.

⚠️ Watch out

Where marks leak

LIKE wildcards: `%` = any number of characters, `_` = exactly one, do not swap them. BETWEEN is inclusive of both ends. Not-equal is written `<>` (or !=). Text values need quotes ('Grocery'), numbers do not. And GROUP BY groups rows for aggregates, it is not the same as ORDER BY (which only sorts). Precise operator use is where SQL marks are won or lost.

📖 Theory

You clicked this in Unit 1

Feel the connection: WHERE is the Filter you ticked in Unit 2, ORDER BY is the Sort, GROUP BY is Consolidate, and DISTINCT is Remove Duplicates. Everything you did by mouse in Excel, you now do by precise typed commands that scale to millions of rows. That is the whole promise of databases over spreadsheets. Next lesson: combining conditions with AND, OR, EXISTS, and naming things with aliases.

✅ Summary

Key takeaways

• WHERE filters rows by a condition using =, >, <, <>, IN, BETWEEN, LIKE, NOT IN.
• LIKE matches patterns: % = any sequence of characters, _ = exactly one character.
• BETWEEN a AND b is INCLUSIVE of both endpoints.
• ORDER BY sorts (ASC default, DESC for high-to-low); DISTINCT removes duplicate values.
• GROUP BY collapses rows sharing a value into groups for per-group summaries.
• Memory hook: WHERE = Excel Filter, ORDER BY = Sort, GROUP BY = Consolidate, now typed.