Hoja de referencia SQL

Basic SELECT structure

SELECT column_a, column_b, AGG(column_c) AS total
FROM table_a
JOIN table_b ON table_a.id = table_b.a_id
WHERE table_a.status = 'active'
GROUP BY column_a, column_b
HAVING AGG(column_c) > 100
ORDER BY total DESC
LIMIT 50;

JOIN types

Aggregate functions

Window functions

-- Running total
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Rank within partitions
SELECT
  department,
  name,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Previous row's value
SELECT
  date,
  amount,
  LAG(amount) OVER (ORDER BY date) AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY date) AS day_over_day_change
FROM transactions;

CTEs (Common Table Expressions)

WITH active_customers AS (
  SELECT id, name FROM customers WHERE status = 'active'
),
recent_orders AS (
  SELECT customer_id, SUM(total) AS total
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '30 days'
  GROUP BY customer_id
)
SELECT ac.name, COALESCE(ro.total, 0) AS spent_30d
FROM active_customers ac
LEFT JOIN recent_orders ro ON ro.customer_id = ac.id
ORDER BY spent_30d DESC;

-- Recursive CTE: organisation hierarchy
WITH RECURSIVE tree AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, t.level + 1
  FROM employees e JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree;

INSERT / UPDATE / DELETE

-- Insert single
INSERT INTO users (name, email) VALUES ('Alice', 'a@x.com');

-- Insert from query
INSERT INTO users_archive (id, name)
SELECT id, name FROM users WHERE created_at < '2024-01-01';

-- Upsert (PostgreSQL)
INSERT INTO users (id, email) VALUES (1, 'a@x.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

-- Update with JOIN-like logic
UPDATE orders SET status = 'shipped'
WHERE id IN (SELECT order_id FROM shipments WHERE shipped_at IS NOT NULL);

-- Delete with safety
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days';
-- Always SELECT first to verify what will be deleted!

Indexes

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_active ON users (created_at) WHERE status = 'active';

Dialect quick reference

Performance hints

• Always run EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) on slow queries — it shows the actual plan and timing.
• SELECT only the columns you need — avoid SELECT * in production code.
• WHERE before JOIN where possible — filtering early reduces the rows that need joining.
• Use EXISTS or IN for membership tests rather than counting all matches.
• Don't wrap indexed columns in functions — that disables the index.
• Run ANALYZE (or its equivalent) after large data loads — keeps stats fresh for the planner.