Hoja de referencia de SQL

40 elementos esenciales con sintaxis y ejemplos. Busca o filtra por grupo.

SELECT / FROM

Elige las columnas que se devuelven y la tabla de origen. SELECT * devuelve todas las columnas.

SELECT col1, col2 FROM table;

SELECT store_name, city, region
FROM stores;

WHERE

Filtra filas antes de agrupar. Combina condiciones con AND / OR / NOT.

SELECT ... FROM t WHERE condition;

SELECT store_name, square_feet
FROM stores
WHERE region = 'South' AND store_type = 'Supermarket';

ORDER BY

Ordena el resultado. ASC es el valor predeterminado.

ORDER BY col [ASC | DESC], ...

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC, product_name;

LIMIT

Devuelve solo las primeras n filas. SQL Server usa TOP n u OFFSET/FETCH; Oracle usa FETCH FIRST n ROWS ONLY.

LIMIT n [OFFSET m]

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 5;

DISTINCT

Elimina filas duplicadas del resultado.

SELECT DISTINCT col FROM t;

SELECT DISTINCT category
FROM products
ORDER BY category;

AS (alias)

Renombra una columna o tabla en la consulta. Los alias de tabla acortan los JOIN.

SELECT expr AS name FROM table AS t

SELECT p.product_name AS product,
       p.unit_price - p.unit_cost AS unit_margin
FROM products AS p;

IN / BETWEEN

IN compara con una lista; BETWEEN prueba un rango inclusivo (funciona con fechas en texto ISO).

col IN (v1, v2) | col BETWEEN a AND b

SELECT order_id, order_date, payment_method
FROM orders
WHERE payment_method IN ('Cash', 'EBT')
  AND order_date BETWEEN '2025-01-01' AND '2025-01-31';

LIKE

Coincidencia de patrones de texto: % = cualquier cantidad de caracteres, _ = un carácter. En SQLite LIKE no distingue mayúsculas (ASCII); en PostgreSQL sí (usa ILIKE).

col LIKE 'pattern'

SELECT product_name, category
FROM products
WHERE product_name LIKE '%chips%';

IS NULL

Comprueba valores faltantes. Nunca uses = NULL: nunca es verdadero.

col IS NULL | col IS NOT NULL

SELECT employee_id, first_name, last_name, role
FROM employees
WHERE manager_id IS NULL;

GROUP BY

Agrupa filas para que las funciones de agregado devuelvan un valor por grupo. Toda columna no agregada del SELECT debe estar en GROUP BY.

SELECT col, AGG(x) FROM t GROUP BY col;

SELECT channel, COUNT(*) AS orders
FROM orders
GROUP BY channel;

HAVING

Filtra grupos después de agregar (WHERE filtra filas antes).

GROUP BY col HAVING AGG(x) condition

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 15
ORDER BY orders DESC;

INNER JOIN

Devuelve solo las filas que tienen coincidencia en ambas tablas.

FROM a JOIN b ON a.key = b.key

SELECT s.region,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount)), 2) AS net_sales
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN stores s ON s.store_id = o.store_id
GROUP BY s.region
ORDER BY net_sales DESC;

LEFT JOIN

Conserva todas las filas de la tabla izquierda; las columnas de la derecha quedan NULL si no hay coincidencia. Útil para encontrar registros sin actividad. Pon las condiciones de la tabla derecha en ON, no en WHERE, o el LEFT JOIN se comporta como INNER JOIN.

FROM a LEFT JOIN b ON a.key = b.key

-- customers with no orders since 2025-10-01
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.order_date >= '2025-10-01'
WHERE o.order_id IS NULL;

Self join

Une una tabla consigo misma, por ejemplo empleados con su gerente.

FROM t AS a JOIN t AS b ON a.parent_id = b.id

SELECT e.first_name || ' ' || e.last_name AS employee,
       m.first_name || ' ' || m.last_name AS manager
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
LIMIT 10;

CROSS JOIN

Todas las combinaciones de filas de ambas tablas. Útil para armar una cuadrícula completa (por ejemplo tienda x categoría) antes de un LEFT JOIN.

FROM a CROSS JOIN b

SELECT s.store_name, c.category
FROM stores s
CROSS JOIN (SELECT DISTINCT category FROM products) c
ORDER BY s.store_name, c.category;

COUNT

COUNT(*) cuenta filas, COUNT(col) cuenta valores no NULL y COUNT(DISTINCT col) cuenta valores únicos.

COUNT(*) | COUNT(col) | COUNT(DISTINCT col)

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT customer_id) AS customers
FROM orders;

SUM

Suma valores numéricos. Puede sumar una expresión, como la venta neta por línea.

SUM(expr)

SELECT ROUND(SUM(quantity * unit_price * (1 - discount)), 2) AS net_sales
FROM order_items;

AVG

Promedio aritmético, ignorando los NULL. En SQLite AVG siempre devuelve un número real.

AVG(expr)

SELECT role, ROUND(AVG(hourly_rate), 2) AS avg_rate
FROM employees
GROUP BY role
ORDER BY avg_rate DESC;

MIN / MAX

Valor más pequeño y más grande; funciona con números, texto y fechas ISO.

MIN(expr) | MAX(expr)

SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS last_order
FROM orders;

CASE

Lógica condicional dentro de una consulta. Combínalo con SUM para totales condicionales.

CASE WHEN cond THEN v1 [WHEN ...] ELSE v2 END

SELECT store_id,
       SUM(CASE WHEN channel = 'Online' THEN 1 ELSE 0 END) AS online_orders,
       SUM(CASE WHEN channel = 'In-store' THEN 1 ELSE 0 END) AS instore_orders
FROM orders
GROUP BY store_id;

COALESCE

Devuelve el primer valor no NULL. Se usa para reemplazar NULL por un valor predeterminado.

COALESCE(v1, v2, ...)

SELECT employee_id, first_name,
       COALESCE(manager_id, 0) AS manager_id
FROM employees
LIMIT 10;

CAST

Convierte un valor a otro tipo (INTEGER, REAL, TEXT...). Evita la división entera: en SQLite 7/2 = 3.

CAST(expr AS type)

SELECT campaign_name,
       ROUND(CAST(clicks AS REAL) / impressions * 100, 2) AS ctr_pct
FROM campaigns
ORDER BY ctr_pct DESC
LIMIT 5;

ROUND

Redondea un número a los decimales indicados (0 por defecto).

ROUND(x [, decimals])

SELECT product_name,
       ROUND((unit_price - unit_cost) / unit_price * 100, 1) AS margin_pct
FROM products
ORDER BY margin_pct DESC;

Subquery

Una consulta dentro de otra, usada como valor, lista o tabla derivada.

WHERE col > (SELECT ...) | FROM (SELECT ...) AS t

SELECT product_name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products)
ORDER BY unit_price DESC;

EXISTS

Es TRUE si la subconsulta devuelve al menos una fila. Eficiente para comprobar si 'tiene algún...'.

WHERE [NOT] EXISTS (SELECT 1 FROM ... WHERE ...)

SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE c.loyalty_member = 1
  AND NOT EXISTS (
      SELECT 1 FROM orders o
      WHERE o.customer_id = c.customer_id AND o.channel = 'Online'
  )
LIMIT 10;

WITH (CTE)

Expresión de tabla común: un resultado temporal con nombre que puedes usar más adelante en la consulta. Hace legibles las consultas largas.

WITH name AS (SELECT ...) SELECT ... FROM name;

WITH order_totals AS (
    SELECT order_id,
           SUM(quantity * unit_price * (1 - discount)) AS order_total
    FROM order_items
    GROUP BY order_id
)
SELECT o.channel,
       ROUND(AVG(t.order_total), 2) AS avg_order_value
FROM order_totals t
JOIN orders o ON o.order_id = t.order_id
GROUP BY o.channel;

WITH RECURSIVE

Una CTE que se referencia a sí misma. Usos típicos: generar una serie de fechas o recorrer una jerarquía.

WITH RECURSIVE r AS (anchor UNION ALL recursive_part) SELECT ...

WITH RECURSIVE months(m) AS (
    SELECT '2025-01-01'
    UNION ALL
    SELECT date(m, '+1 month') FROM months WHERE m < '2025-12-01'
)
SELECT m FROM months;

ROW_NUMBER

Numera las filas 1, 2, 3... dentro de cada partición. Uso clásico: tomar la primera o la última fila de cada grupo.

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

WITH ranked AS (
    SELECT customer_id, order_id, order_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
    FROM orders
)
SELECT customer_id, order_id, order_date AS first_order
FROM ranked
WHERE rn = 1
LIMIT 10;

RANK / DENSE_RANK

Ranking con empates. RANK deja huecos después de un empate (1,1,3); DENSE_RANK no (1,1,2).

RANK() OVER (ORDER BY ...) | DENSE_RANK() OVER (...)

SELECT category, product_name, unit_price,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY unit_price DESC) AS price_rank
FROM products;

SUM() OVER (running total)

Un agregado usado como ventana: con ORDER BY se convierte en un total acumulado.

SUM(x) OVER (ORDER BY ... [ROWS BETWEEN ...])

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS sales
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date LIKE '2025%'
    GROUP BY month
)
SELECT month,
       ROUND(sales, 2) AS sales,
       ROUND(SUM(sales) OVER (ORDER BY month), 2) AS sales_ytd
FROM monthly;

Share of total (PARTITION BY)

Un agregado de ventana sin ORDER BY devuelve el total del grupo en cada fila, así calculas porcentajes sin una segunda consulta.

x / SUM(x) OVER (PARTITION BY ...)

SELECT region, store_name, square_feet,
       ROUND(100.0 * square_feet / SUM(square_feet) OVER (PARTITION BY region), 1) AS pct_of_region
FROM stores
ORDER BY region, pct_of_region DESC;

LAG / LEAD

Lee un valor de una fila anterior (LAG) o siguiente (LEAD). Ideal para el cambio contra el mes anterior.

LAG(x [, n, default]) OVER (ORDER BY ...)

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS sales
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY month
)
SELECT month,
       ROUND(sales, 2) AS sales,
       ROUND(sales - LAG(sales) OVER (ORDER BY month), 2) AS change_vs_prev
FROM monthly;

Moving average (ROWS frame)

El marco de ventana limita qué filas se incluyen. Aquí: el mes actual y los dos anteriores.

AVG(x) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW)

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS sales
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY month
)
SELECT month,
       ROUND(AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma_3m
FROM monthly;

NTILE

Divide las filas ordenadas en n grupos de tamaño similar (por ejemplo cuartiles).

NTILE(n) OVER (ORDER BY ...)

SELECT customer_id, COUNT(*) AS orders,
       NTILE(4) OVER (ORDER BY COUNT(*) DESC) AS quartile
FROM orders
GROUP BY customer_id
LIMIT 10;

strftime

SQLite: formatea o extrae partes de una fecha (%Y año, %m mes, %d día, %w día de la semana 0=domingo). PostgreSQL usa EXTRACT/TO_CHAR/DATE_TRUNC; SQL Server usa YEAR(), MONTH(), FORMAT().

strftime(format, date)

SELECT strftime('%Y', order_date) AS year,
       COUNT(*) AS orders
FROM orders
GROUP BY year;

date() modifiers

SQLite: aritmética de fechas con modificadores de texto. PostgreSQL usa date + INTERVAL '1 month'; SQL Server usa DATEADD(month, 1, d).

date(value, '+n days' | '-n months' | 'start of month' ...)

SELECT campaign_name, start_date,
       date(start_date, 'start of month') AS month_start,
       date(start_date, '+30 days') AS plus_30
FROM campaigns
LIMIT 5;

julianday

SQLite: diferencia entre fechas en días. PostgreSQL: d2 - d1; SQL Server: DATEDIFF(day, d1, d2).

julianday(d2) - julianday(d1)

SELECT campaign_name,
       julianday(end_date) - julianday(start_date) + 1 AS days_running
FROM campaigns
ORDER BY days_running DESC
LIMIT 5;

UNION / UNION ALL

Apila los resultados de dos consultas con las mismas columnas. UNION elimina duplicados; UNION ALL los conserva (y es más rápido).

SELECT ... UNION [ALL] SELECT ...

SELECT city, 'Store' AS source FROM stores
UNION
SELECT city, 'Customer' FROM customers
ORDER BY city
LIMIT 10;

INTERSECT

Filas que aparecen en ambos resultados.

SELECT ... INTERSECT SELECT ...

SELECT customer_id FROM orders WHERE channel = 'Online'
INTERSECT
SELECT customer_id FROM orders WHERE channel = 'In-store'
LIMIT 10;

EXCEPT

Filas del primer resultado que no están en el segundo. Oracle lo llama MINUS.

SELECT ... EXCEPT SELECT ...

-- customers with no orders in the second half of 2025
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2025-07-01';