Subconsulta que devuelve varios campos

Seguro que alguna vez se nos ha presentado la necesidad de hacer una consulta SQL que devuelva varios campos y no hemos podido porque los intérpretes de SQL no son capaces de hacer algo como esto:

select t.campo1
       ,(select campo2, campo3 from tabla_aux where id = t.id) campo2, campo3
from tabla t
where t.id = 123456

Al ver que el intérprete no es capaz, tenemos que acudir a las subconsultas con relaciones join y demás para poder recuperar la información que necesitamos, llegando en algunos casos a tener unas SQL muy grandes y que son muy lentas. La solución que yo he encontrado y que es la más optima en rendimiento que he conseguido hacer es la siguiente.

Supongamos una tabla de histórico de direcciones de personas, donde guardamos por cada NIF todas las direcciones donde ha vivido, y queremos obtener un listado de personas con su última dirección. Es decir, por cada grupo de registros de cada persona, necesitamos recuperar el último.

Para conseguir lo anterior necesitamos saber lo siguiente:

• Cada dirección la guardaremos con un identificador "num_dir" que será un secuencial.
• La primary key (PK) en este caso estaría compuesto por estos dos campos (NIF, NUM_DIR).
• La pareja de datos que queremos recuperar es "NIF, Max(NUM_DIR)".

Con el ejemplo anterior "incorrecto" seguramente habríamos intentado algo como esto:

select d.nif
       ,(select nif, max(num_dir), ciudad, calle from direcciones where nif = d.nif) nif, num_dir, ciudad, calle
from direcciones d

pero la subselect no nos deja devolver más de un campo por lo que debemos recurrir a otro tipo de solución más compleja.

La consulta resultante sería esta:

SELECT d.nif
      -- Este campo es un secuencial que va incrementando por cada vez que cambie de domicilio
      ,d.num_dir
      -- Este es el maximo de cada grupo. Lo saco por cada registro.
      ,du.num_dir
      ,d.ciudad
      ,d.calle
      ,d.numero
      ,d.piso
FROM direcciones d
     -- Busco el máximo de cada grupo (la última dirección de la persona)
     LEFT JOIN (SELECT t.nif, MAX(t.num_dir) num_dir
                FROM direcciones t
                GROUP BY t.nif
               ) du ON du.nif = d.nif
WHERE -- Selecciono los registros que coinciden con el máximo
      d.num_dir = du.num_dir

Si quitamos la condición del where "d.num_dir = du.num_dir" la consulta devolverá todas las direcciones por cada NIF y en la columna du.num_dir siempre devolverá el valor máximo. Para saber qué datos necesitamos recuperar, bastaría con incluir esa condición en el where.

Para comprobar que los datos que se recuperan son correctos, lanzar la consulta sin el where y con el where y comparar. De esa forma os aseguráis que la consulta está construida correctamente.

Esta solución valdría si quieremos el máximo, el mínimo, la media... pero si queremos por ejemplo el 3º registro por orden alfábetico habría que incluir el rownum en la select del left join y encapsularla seguramente en otra select para que no deje seleccionar el n-ésimo elemento.