02 — SELECT Basics & Filtering

-- projection: which columns to return
-- selection:  which rows to return (WHERE)
-- source:     which table (or join/CTE/subquery) to read from
SELECT column1, column2
FROM table_name
WHERE condition;

-- Explicit columns — the only safe form for application code.
-- The planner can use covering indexes that include exactly these columns.
SELECT name, city FROM customers;

-- Expressions with aliases
SELECT
  name,
  length(name) AS name_len,               -- function call + alias
  upper(city) AS city_upper
FROM customers;

-- Constants and computations
SELECT
  name,
  'US' AS country,                        -- literal column
  2024 - 2000 AS age_estimate             -- arithmetic on literals
FROM customers;

-- ❌ WRONG: SELECT * in production application code
SELECT * FROM customers;

-- ✅ RIGHT: explicit column list
SELECT id, name, city, created_at FROM customers;

-- AS is optional but improves readability — always use it in production code
SELECT amount * 1.08 AS total_with_tax
FROM orders;

-- Quoted alias when it contains spaces or mixed case
SELECT amount * 1.08 AS "Total With Tax"   -- quoted: preserves case and spaces
FROM orders;

-- Alias scoping: visible in ORDER BY (runs after SELECT), NOT in WHERE (runs before)
SELECT amount * 1.08 AS taxed
FROM orders
-- WHERE taxed > 100;          -- ❌ ERROR: column "taxed" does not exist
ORDER BY taxed DESC;           -- ✅ works — ORDER BY runs after SELECT

-- Unique cities — DISTINCT applies to the entire row, not individual columns
SELECT DISTINCT city FROM customers;

-- Distinct combinations — returns unique (city, name) pairs, not unique cities + unique names
SELECT DISTINCT city, name FROM customers;

-- The largest order per customer — "greatest-N-per-group" in one query.
-- DISTINCT ON (customer_id) keeps the first row per customer_id group.
-- ORDER BY must start with the DISTINCT ON columns, then the ranking within each group.
SELECT DISTINCT ON (customer_id)
  customer_id, id, amount
FROM orders
ORDER BY customer_id, amount DESC;   -- within each customer, sort by amount DESC → keep the biggest

SELECT name, city FROM customers WHERE city = 'NYC';
SELECT name FROM customers WHERE name <> 'Alice';
SELECT * FROM orders WHERE amount >= 100;
SELECT * FROM orders WHERE amount BETWEEN 10 AND 100;   -- 10 <= amount <= 100 (inclusive)
SELECT * FROM orders WHERE customer_id IN (1, 3);
SELECT * FROM customers WHERE name LIKE 'A%';            -- starts with 'A'
SELECT * FROM customers WHERE name LIKE '_a%';           -- second char is 'a'
SELECT * FROM customers WHERE city IS NULL;              -- correct NULL test
SELECT * FROM customers WHERE city IS DISTINCT FROM 'NYC'; -- NULL-safe "not NYC" — includes NULL cities

-- ❌ Unclear — relies on remembering AND > OR precedence
SELECT * FROM orders
WHERE customer_id = 1 OR customer_id = 2 AND amount > 100;

-- ✅ Explicit — intent is obvious
SELECT * FROM orders
WHERE customer_id = 1 OR (customer_id = 2 AND amount > 100);

-- ✅ Different intent entirely
SELECT * FROM orders
WHERE (customer_id = 1 OR customer_id = 2) AND amount > 100;

-- Escape literal wildcards using ESCAPE clause
SELECT * FROM users WHERE name LIKE '%\_%' ESCAPE '\';   -- match literal underscore in name

-- pg_trgm trigram index supports %foo% with index acceleration
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

SELECT * FROM users WHERE name LIKE '%alice%';   -- now uses the GIN trigram index

-- IN with a subquery — a "semi-join": keep left rows that have at least one match in the subquery
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- EXISTS — same result, different execution strategy
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id   -- SELECT 1: we only care about existence, not values
);

-- NOT EXISTS — immune to NULL poisoning (unlike NOT IN)
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

SELECT * FROM customers WHERE city = NULL;     -- ❌ returns nothing — NULL = NULL is UNKNOWN
SELECT * FROM customers WHERE city IS NULL;    -- ✅ correct NULL test
SELECT * FROM customers WHERE city != 'NYC';   -- ❌ excludes NULLs too! NULL != 'NYC' is UNKNOWN

-- IS DISTINCT FROM — NULL-safe "not equal": treats NULL = NULL as equal (not distinct)
SELECT * FROM customers WHERE city IS DISTINCT FROM 'NYC';
-- Returns rows where city != 'NYC' OR city IS NULL — includes unknown cities

-- IS NOT DISTINCT FROM — NULL-safe "equal": NULL IS NOT DISTINCT FROM NULL → TRUE
SELECT * FROM orders WHERE amount IS NOT DISTINCT FROM NULL;
-- Equivalent to: WHERE amount IS NULL, but useful in generic/comparison code

-- ❌ ERROR: alias "taxed" doesn't exist in WHERE (WHERE runs before SELECT)
SELECT amount * 1.08 AS taxed FROM orders WHERE taxed > 100;

-- ✅ Repeat the expression
SELECT amount * 1.08 AS taxed FROM orders WHERE amount * 1.08 > 100;

-- ✅ Or use a CTE to compute once, filter twice (cleaner for complex expressions)
WITH taxed_orders AS (
  SELECT *, amount * 1.08 AS taxed FROM orders
)
SELECT * FROM taxed_orders WHERE taxed > 100;

-- A realistic report query: find high-value customers in major cities
-- who haven't placed any orders over $500 in the last 90 days.
SELECT
  c.id,
  c.name,
  c.city,
  c.created_at
FROM customers c
WHERE c.city IN ('NYC', 'LA', 'SF')                         -- restrict to major cities (uses index)
  AND c.name LIKE 'A%'                                       -- name filter (index-accelerated if no leading %)
  AND c.deleted_at IS NULL                                    -- exclude soft-deleted
  AND NOT EXISTS (                                            -- anti-join: no large recent orders
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
      AND o.amount > 500
      AND o.ordered_on >= CURRENT_DATE - INTERVAL '90 days'
  )
ORDER BY c.created_at DESC;

-- ❌ WRONG: SELECT * in a production query
-- Problems: fetches all columns (wasteful), breaks if schema changes,
-- prevents index-only scans, exposes sensitive columns (e.g., password_hash)
SELECT * FROM customers WHERE city = 'NYC';

-- ✅ RIGHT: explicit columns
SELECT id, name, city, created_at FROM customers WHERE city = 'NYC';

-- Large IN list — can be slow to parse and hit limits
SELECT * FROM orders WHERE customer_id IN (1, 2, 3, /* ... 5000 values */);

-- Better for large lists — ARRAY is passed as a single parameter, parsed once
SELECT * FROM orders WHERE customer_id = ANY(ARRAY[1, 2, 3, /* ... 5000 values */]::bigint[]);

-- ❌ NULL poison: if the subquery returns any NULL, the entire NOT IN returns zero rows
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE city IS NULL);

-- ✅ Safe: NOT EXISTS is immune to NULLs in the subquery
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.city IS NULL);

SELECT name FROM customers WHERE city != 'NYC';