SQL SELECT DISTINCT

-- All unique countries our users come from
SELECT DISTINCT country
FROM users;

-- DISTINCT applies to the WHOLE row (all listed columns)
SELECT DISTINCT country, state
FROM users
ORDER BY country, state;
-- AU + NSW is different from AU + VIC


-- Counting distinct values
SELECT COUNT(DISTINCT country) AS unique_countries
FROM users;


-- DISTINCT vs GROUP BY
-- These two queries return the same result:
SELECT DISTINCT country FROM users;
SELECT country FROM users GROUP BY country;
-- GROUP BY scales better when you also need aggregates


-- Common mistake - duplicate after a join
SELECT u.id, u.email
FROM users u
JOIN orders o ON o.user_id = u.id;
-- if a user has 3 orders, their row appears 3 times

-- Fix: DISTINCT
SELECT DISTINCT u.id, u.email
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Better fix: use EXISTS or a CTE
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);


-- DISTINCT ON - Postgres extension for 'first per group'
SELECT DISTINCT ON (user_id) user_id, id AS first_order_id, created_at
FROM orders
ORDER BY user_id, created_at;
-- one row per user_id - the earliest order


-- Performance note
-- DISTINCT can be expensive on large result sets.
-- It usually requires an internal sort or hash to dedupe.
-- Index the columns being deduped if it shows up in EXPLAIN as a slow step.

SELECT DISTINCT country
FROM customers;