Queries & Joins

Basic SELECT

-- Basic query
SELECT id, name, email FROM users WHERE active = true ORDER BY name LIMIT 10 OFFSET 20;

-- Aliases and expressions
SELECT
  first_name || ' ' || last_name AS full_name,
  EXTRACT(YEAR FROM created_at) AS year,
  COALESCE(phone, 'N/A') AS phone
FROM users;

-- Distinct
SELECT DISTINCT department FROM employees;

-- Aggregate functions
SELECT
  department,
  COUNT(*) AS total,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

JOIN Types

-- INNER JOIN — only matching rows
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN — all from left, NULLs for unmatched right
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- RIGHT JOIN — all from right
SELECT u.name, o.total
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- FULL OUTER JOIN — all rows from both
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN — cartesian product
SELECT p.name, c.name AS color
FROM products p
CROSS JOIN colors c;

-- Self join
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Subqueries

-- Subquery in WHERE
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- Correlated subquery
SELECT name, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE department = e.department
);

-- EXISTS
SELECT name FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders WHERE user_id = u.id AND status = 'pending'
);

-- Subquery in FROM (derived table)
SELECT dept, avg_salary
FROM (
  SELECT department AS dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) sub
WHERE avg_salary > 50000;

CTEs (Common Table Expressions)

-- Basic CTE
WITH recent_orders AS (
  SELECT user_id, COUNT(*) AS cnt
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, ro.cnt
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id;

-- Multiple CTEs
WITH
  active_users AS (SELECT id FROM users WHERE active = true),
  user_orders AS (SELECT user_id, SUM(total) AS revenue FROM orders GROUP BY user_id)
SELECT u.id, uo.revenue
FROM active_users u
JOIN user_orders uo ON u.id = uo.user_id;

-- Recursive CTE — org chart / tree traversal
WITH RECURSIVE org_tree AS (
  -- Base case: top-level employees
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- Recursive case
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth, name;

Useful Functions

-- String functions
SELECT
  UPPER(name), LOWER(email),
  LENGTH(name),
  SUBSTRING(email FROM 1 FOR 5),
  TRIM('  hello  '),
  REPLACE(name, 'Ltd', 'Limited'),
  CONCAT(first_name, ' ', last_name),
  SPLIT_PART(email, '@', 2) AS domain
FROM users;

-- Date functions
SELECT
  NOW(), CURRENT_DATE, CURRENT_TIME,
  AGE(NOW(), created_at) AS account_age,
  DATE_TRUNC('month', created_at) AS month,
  TO_CHAR(created_at, 'YYYY-MM-DD') AS formatted
FROM users;

-- Conditional
SELECT
  name,
  CASE
    WHEN salary > 100000 THEN 'senior'
    WHEN salary > 60000 THEN 'mid'
    ELSE 'junior'
  END AS level,
  NULLIF(phone, '') AS phone,  -- returns NULL if empty string
  GREATEST(a, b, c),
  LEAST(a, b, c)
FROM employees;