CASE and set ops

What you will be able to do

• Build CASE expressions for buckets and labels
• Stack rows with UNION ALL
• Know when UNION removes duplicates

Why this matters for data work

CASE encodes business rules in SQL (status groups, revenue tiers). UNION ALL stacks similar-shaped results (this year vs last year files, multiple sources).

Concepts

• CASE WHEN … THEN … ELSE … END
• UNION ALL — keep duplicates; faster.
• UNION — distinct rows; costlier.
• Same column count/types across sides.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
SELECT
  order_id,
  line_total,
  CASE
    WHEN line_total < 5 THEN 'small'
    WHEN line_total < 15 THEN 'medium'
    ELSE 'large'
  END AS size_bucket
FROM orders
WHERE status = 'completed'
ORDER BY line_total DESC
LIMIT 10;

SELECT 'completed' AS kind, count(*) AS n FROM orders WHERE status = 'completed'
UNION ALL
SELECT 'returned', count(*) FROM orders WHERE status = 'returned'
UNION ALL
SELECT 'cancelled', count(*) FROM orders WHERE status = 'cancelled';
SQL

What you should see

• Size buckets on high-value lines.
• Three rows of status counts.

Common mistakes

• UNION when you meant UNION ALL — surprise distinct.
• CASE without ELSE — NULL fall-through.
• Mismatched columns in set ops.

Next

DDL and load