Juntas Entre Tablas

Hasta ahora, nuestras consultas han accedido solo a una tabla al mismo tiempo. Las consultas pueden acceder a múltiples tablas a la vez, o acceder a la misma tabla en una forma que múltiple filas de la tabla sean procesadas al mismo tiempo. Una consulta que accede a multiples filas de la misma o de diferentes tablas al mismo tiempo se llama una junta. Como ejemplo, digamos que queremos listar todos los registros del clima junto con la ubicación de la ciudad asociada. Para hacerlo, necesitamos comparar la columna ciudad de cada fila de la tabla clima, con la columna nombre de todas las filas en la tabla ciudades, y seleccionar los pares de filas cuyos valores coincidan.

Nota: Esto es solo un modelo conceptual. La junta es realizada de una manera más eficiente que realmente comparar cada posible par de filas, pero esto es invisible al usuario.

Esto sería logrado con la siguiente columna:

SELECT *
    FROM clima, ciudades
    WHERE ciudad = nombre;

     ciudad    | temp_min | temp_max | prcp |   fecha    |    nombre     | ubicacion
---------------+----------+----------+------+------------+---------------+-----------
 San Francisco |       46 |       50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |       43 |       57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(2 filas)

Observe dos cosas sobre el conjunto resultado:

• No hay fila resultado para la ciudad de Hayward. Esto es porque no hay una entrada coincidente en la tabla ciudades para Hayward, por lo que la junta ignora las filas sin coincidencia de la tabla clima. Veremos en breve como se puede resolver este tema.

• Hay dos columnas conteniendo el nombre de la ciudad. Esto es correcto porque las listas de columnas de las tablas clima y ciudades se concatenan. En la práctica esto no es deseable, por lo tanto, probablemente deseará listar las columnas de salida explicitamente en vez de usar *:

SELECT ciudad, temp_min, temp_max, prcp, fecha, ubicacion
    FROM clima, ciudades
    WHERE ciudad = nombre;

Ejercicio: Intente determinar la semántica de esta consulta cuando la clausula WHERE es omitida.

Dado que todas las columnas tienen nombres diferentes, el analizador automáticamente encuentra a que tabla pertenece cada una. Si hubiera nombres duplicados de columna en las dos tablas necesitaría calificar los nombres de columna para mostrar a que tabla se refiere, como en:

SELECT clima.ciudad, clima.temp_min, clima.temp_max,
       clima.prcp, clima.fecha, ciudades.ubicacion
    FROM clima, ciudades
    WHERE ciudades.nombre = clima.ciudad;

Es ampliamente considerado un buen estilo el calificar todos los nombres de columnas en una consulta de combinación, ya que entonces la consulta no fallará si un nombre de columna duplicado es agregado luego a una de las tablas.

Las consultas de combinación del tipo visto hasta aquí pueden ser también escritas en esta forma alternativa:

SELECT *
    FROM clima INNER JOIN ciudades ON (clima.ciudad = ciudades.nombre);

Esta sintaxis no es tan comunmente usada como la anterior, pero la mostramos aqui para ayudar a entender los siguientes temas.

Ahora vamos a ver como podemos obtener los registros Hayward de vuelta. Lo que queremos que la consulta haga es que recorra la tabla clima y para cada fila que encuentre las columnas de ciudades que coincidan. Si no hay una fila que coincida queremos que algún "valor vacio" sea sustituido por las columnas de la tabla ciudades. Este tipo de consulta es llamado una junta externa. (Las juntas que hemos visto hasta aqui son juntas interiores.) El comando se ve asi:

SELECT *
    FROM clima LEFT OUTER JOIN ciudades ON (clima.ciudad = ciudades.nombre);

    ciudad     | temp_min | temp_max | prcp |   fecha    |    nombre     | ubicacion
---------------+----------+----------+------+------------+---------------+-----------
 Hayward       |       37 |       54 |      | 1994-11-29 |               |
 San Francisco |       46 |       50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |       43 |       57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 filas)

Esta consulta se llama junta externa izquierda dado que la tabla mencionada a la izquierda del operador de junta tendra al menos una vez cada una de sus filas en la salida, mientras que la tabla a la derecha solo tendrá en la salida aquellas filas que coincidan con alguna de la tabla izquierda. Cuando se de salida a filas de la tabla izquierda para las cuales no hay coincidencia en la tabla derecha, se sustituiran las columnas de la tabla derecha por valores vacios (nulos).

Ejercicio: También hay juntas externas derechas y juntas externas completas. Trate de averiguar que hacen cada una.

También podemos juntar una tabla contra si misma. Esto se llama autocombinación. Como ejemplo, supongamos que queremos encontrar todos los registros del clima que estan en el rango de otros registros. Por lo que queremos comparar las columnas temp_min y temp_max de cada fila clima contra las columnas temp_min and temp_max de todas las otras filas de clima. Podemos hacer esto con la siguiente consulta:

SELECT C1.ciudad, C1.temp_min AS min, C1.temp_max AS max,
    C2.ciudad, C2.temp_min AS min, C2.temp_max AS max
    FROM clima C1, clima C2
    WHERE C1.temp_min < C2.temp_min
    AND C1.temp_max > C2.temp_max;

    ciudad     | min | max |    ciudad     | min | max
---------------+-----+-----+---------------+-----+-----
 San Francisco |  43 |  57 | San Francisco |  46 |  50
 Hayward       |  37 |  54 | San Francisco |  46 |  50
(2 filas)

Aquí hemos reetiquetado la tabla clima como C1 and C2 para poder distinguir el lado izquierdo y derecho de la junta. También se puede usar este tipo de alias en otras consultas para ahorrar cierto tipeo, ej.:

SELECT *
    FROM clima w, ciudades c
    WHERE w.ciudad = c.nombre;

Encontrará este estilo de abreviatura bastante frecuentemente.