SELECT, WHERE and CASE

SELECT, WHERE, and CASE are the basic tools for asking a database a question and shaping the answer.

SELECT chooses which columns you get back. Write SELECT * and you get every column; name columns, like SELECT country, plan, and you get only those. SELECT controls the shape of the result, not which rows are included.

WHERE filters which rows you get back. It keeps only rows that meet a condition and drops the rest. For example, WHERE plan = 'pro' keeps only paid users, and WHERE country = 'US' keeps only users in the US. This is the key split: SELECT picks columns, WHERE picks rows.

CASE creates a new derived column by labeling or bucketing rows with conditions. The pattern is CASE WHEN ... THEN ... ELSE ... END. For example, you can compute a label called tier: return 'paid' when plan = 'pro', otherwise 'free'. Or you can bucket a number into 'low', 'medium', or 'high'. CASE does not change the stored data; it computes a value on the fly for the result.

One habit to avoid: the ELSE here labels anything that is not 'pro' as 'free', including a NULL plan or a typo like 'Pro'. A safer pattern ends with ELSE 'unknown', so bad data shows up as unknown instead of silently joining a real bucket.

These three show up in everyday SQL. Most queries start by choosing columns with SELECT, filtering rows with WHERE, and sometimes adding a CASE label, before any joining or grouping happens.

Filtering rows live

SELECT user_id, country, plan, signup_date
FROM users;