Using SELECT from SELECT

Algo interesante de SQL es que el resultado de una consulta de tipo SELECT puede ser usado como el conjunto de registros sobre el que podríamos ejecutar otro SELECT. A esto se le conoce como: Hacer un SELECT sobre una tabla derivada. Por ejemplo, supongamos que tenemos un grupo de tablas sobre las que hay que aplicar ciertos filtros y relacionar algunos registros por sus claves primarias y foráneas.

Al aplicar sobre un grupo de tablas una serie de sentencias DML obtendremos como resultado una única tabla que consolida todos los campos que hemos querido recuperar, esa es la esencia de un SELECT. Dicho resultado va a quedar almacenado en una tabla en memoria a la que llamaremos T. Lo interesante es que podemos volver a ejecutar un SELECT sobre T aplicando ciertos criterios como si fuese una tabla de la base de datos normal. 

Esto es realmente útil cuando, por ejemplo, tenemos dos tablas: ta y tb. Y supongamos que tenemos que aplicar tres filtros f1, f2 y f3 pero el 80% de tb no cumple con el filtro f3. Entonces no tendría sentido utilizar dicha tabla completa en el SELECT general pues sería mejor eliminar ese 80% antes de la operación principal. La consulta, según lo explicado hasta ahora, quedaría algo así:

Select * from ta, (Select * from tb Where tb.f3=f3) tc Where ta.f1=f1 And tc.f2=f2

Otro ejemplo de la utilización de un 'SELECT from SELECT' es cuando la cantidad de criterios a filtrar es tan grande que hace ilegible una consulta. Para esto podríamos ir aplicando sistemáticamente los filtros para ir reduciendo, subconsulta a subconsulta, la cantidad de datos a evaluar.

Y como no hay nada mejor que un ejemplo pues aquí les traigo un caso de aplicación. Supongamos que trabajamos en una empresa de transportes y tenemos una base de datos en donde se guardan nuestros vehículos, los pilotos y los mantenimientos que se le hace a cada vehículo. La base de datos que soporta esto sería algo así:

Y su script de MySQL sería el siguiente.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `rolandopalermo` ;
CREATE SCHEMA IF NOT EXISTS `rolandopalermo` DEFAULT CHARACTER SET utf8 ;
USE `rolandopalermo` ;

-- -----------------------------------------------------
-- Table `rolandopalermo`.`tb_piloto`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rolandopalermo`.`tb_piloto` ;

CREATE  TABLE IF NOT EXISTS `rolandopalermo`.`tb_piloto` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `nombre` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `rolandopalermo`.`tb_modelo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rolandopalermo`.`tb_modelo` ;

CREATE  TABLE IF NOT EXISTS `rolandopalermo`.`tb_modelo` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `descripcion` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `rolandopalermo`.`tb_vehiculos`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rolandopalermo`.`tb_vehiculos` ;

CREATE  TABLE IF NOT EXISTS `rolandopalermo`.`tb_vehiculos` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `numero_placa` VARCHAR(45) NOT NULL ,
  `fecha_registro` DATE NOT NULL ,
  `id_modelo` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `FK_tb_vehiculos_modelos` (`id_modelo` ASC) ,
  CONSTRAINT `FK_tb_vehiculos_modelos`
    FOREIGN KEY (`id_modelo` )
    REFERENCES `rolandopalermo`.`tb_modelo` (`id` ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `rolandopalermo`.`tb_mantenimiento`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rolandopalermo`.`tb_mantenimiento` ;

CREATE  TABLE IF NOT EXISTS `rolandopalermo`.`tb_mantenimiento` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `id_vehiculo` INT(10) UNSIGNED NOT NULL ,
  `fecha_mantenimiento` DATE NOT NULL ,
  `id_piloto` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `FK_tb_mantenimiento_vehiculo` (`id_vehiculo` ASC) ,
  INDEX `FK_tb_mantenimiento_piloto` (`id_piloto` ASC) ,
  CONSTRAINT `FK_tb_mantenimiento_piloto`
    FOREIGN KEY (`id_piloto` )
    REFERENCES `rolandopalermo`.`tb_piloto` (`id` ),
  CONSTRAINT `FK_tb_mantenimiento_vehiculo`
    FOREIGN KEY (`id_vehiculo` )
    REFERENCES `rolandopalermo`.`tb_vehiculos` (`id` ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Y ahora tenemos un requerimiento de los siempre 'oportunos' analistas (es broma, no tengo nada contra ellos jajaja):

Se necesita un reporte que liste todos los mantenimientos hechos hasta ahora en el cual se pueda visualizar la placa del vehículo, el nombre de su modelo, el piloto que hizo el mantenimiento y la fecha de mantenimiento.

Como podemos ver son 4 tablas a cruzar y con muchos criterios de selección.

SELECT t2.placa,
       t2.modelo,
       p.nombre,
       t2.fecha_mantenimiento
FROM
  (SELECT t1.c1 placa,
          mo.descripcion modelo,
          t1.c3,
          t1.c4 fecha_mantenimiento
   FROM
     (SELECT v.numero_placa c1,
             v.id_modelo c2,
             m.id_piloto c3,
             m.fecha_mantenimiento c4
      FROM tb_vehiculos v,
           tb_mantenimiento m
      WHERE m.id_vehiculo=v.id) t1,
                                tb_modelo mo
   WHERE t1.c2=mo.id) t2,
                      tb_piloto p
WHERE t2.c3=p.id;

Y el resultado de la ejecución es el siguiente:

Primero formamos la tabla que tiene los datos del mantenimiento que se hace a cada vehículo, la cual llamaremos t1. Cruzamos esta tabla de mantenimientos por vehículo (t1) con la tabla de Modelos de Vehículo para obtener la descripción de este último. A esta nueva tabla derivada llamaremos t2. Ahora, como podemos ver, ya tenemos una nueva tabla con mayor información de mantenimientos por vehículos (t2), la misma que solo contiene los ID de los pilotos que han hecho algún mantenimiento a su unidad. Con esto cruzamos la tabla derivada t2 con la de pilotos y obtenemos el reporte que se estuvo buscando.

Espero que este artículo les ayude a comprender un poco mejor el tema de tablas derivadas, sin embargo cualquier consulta no duden en comentarla. Saludos.