SQL: Sintaxis avanzada

La sintaxis avanzada de SQL permite a los desarrolladores realizar consultas complejas y optimizar el rendimiento de las bases de datos. A continuación, se presentan algunos conceptos y ejemplos clave para dominar esta área.

Subconsultas

Las subconsultas o consultas anidadas son consultas dentro de otra consulta SQL. Estas son útiles para realizar operaciones que requieren múltiples pasos.

SELECT nombre, salario
FROM empleados
WHERE salario > (SELECT AVG(salario) FROM empleados);

En este ejemplo, la subconsulta (SELECT AVG(salario) FROM empleados) calcula el salario promedio de todos los empleados, y la consulta externa selecciona aquellos empleados cuyos salarios son mayores que el promedio.

Uniones (JOIN)

Las uniones permiten combinar filas de dos o más tablas basadas en una condición relacionada entre ellas. Los tipos más comunes de uniones son INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL JOIN.

Inner join

SELECT empleados.nombre, departamentos.nombre
FROM empleados
INNER JOIN departamentos ON empleados.departamento_id = departamentos.id;

Esta consulta selecciona los nombres de empleados y departamentos donde hay una coincidencia en departamento_id.

Left join

SELECT empleados.nombre, departamentos.nombre
FROM empleados
LEFT JOIN departamentos ON empleados.departamento_id = departamentos.id;

El LEFT JOIN devuelve todas las filas de la tabla empleados, y las filas coincidentes de departamentos. Si no hay coincidencia, el resultado es NULL en la parte de departamentos.

Funciones de ventana

Las funciones de ventana permiten realizar cálculos sobre un conjunto de filas relacionadas con la fila actual. Ejemplos comunes incluyen ROW_NUMBER(), RANK(), y DENSE_RANK().

Row_number()

SELECT nombre, salario,
       ROW_NUMBER() OVER (PARTITION BY departamento_id ORDER BY salario DESC) as fila
FROM empleados;

Esta consulta asigna un número de fila a cada empleado dentro de su departamento, ordenando por salario en orden descendente.

CTE (Common Table Expressions)

Las expresiones de tabla comunes (CTE) son una forma de definir consultas temporales que pueden ser referenciadas dentro de una consulta SELECT, INSERT, UPDATE, o DELETE.

WITH EmpleadosConSalario AS (
    SELECT nombre, salario
    FROM empleados
    WHERE salario > 50000
)
SELECT nombre
FROM EmpleadosConSalario
WHERE nombre LIKE 'A%';

En este ejemplo, la CTE EmpleadosConSalario selecciona empleados con salarios mayores a 50000, y la consulta externa filtra aquellos cuyos nombres comienzan con 'A'.

Índices y optimización

El uso adecuado de índices es crucial para mejorar el rendimiento de las consultas SQL. Los índices pueden ser únicos, compuestos o de texto completo.

Crear un índice

CREATE INDEX idx_salario ON empleados(salario);

Este comando crea un índice en la columna salario de la tabla empleados, mejorando la rapidez de las consultas que filtran o ordenan por esa columna.

Manejo de transacciones

Las transacciones permiten agrupar una o más operaciones SQL en una única unidad de trabajo, asegurando que todas las operaciones se completen con éxito o ninguna lo haga.

Iniciar una transacción

BEGIN TRANSACTION;

UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;

COMMIT;

Aquí, se inicia una transacción que transfiere 100 unidades monetarias de una cuenta a otra. Si ambas operaciones tienen éxito, la transacción se confirma con COMMIT.