06.-Uniones de tablas

Hasta ahora, nuestras consultas sólo han accedido a una mesa a la vez. Las consultas pueden acceder a varias mesas a la vez, o acceder a la misma tabla de tal manera que se están procesando varias filas de la tabla al mismo tiempo. Las consultas que acceden a múltiples tablas (o múltiples instancias de la misma mesa) en un momento se llaman a consultas de unirse. Combinan filas de una tabla con filas de una segunda tabla, con una expresión que especifica qué filas deben ser emparejados. Por ejemplo, para devolver todos los registros meteorológicos junto con la ubicación de la ciudad asociada, la base de datos necesita comparar la citycolumna de cada hilera de la weathermesa con el namecolumna de todas las filas en el citiesmesa y seleccione los pares de filas donde coinciden estos valores.¹ Esto se lograría con la siguiente consulta:

SELECT * FROM weather JOIN cities ON city = name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 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 rows)

Observe dos cosas sobre el conjunto de resultados:

• No hay ninguna fila de resultados para la ciudad de Hayward. Esto se debe a que no hay ninguna entrada a juego en el citiesmesa para Hayward, así que la unión ignora las filas inigualables en el weathermesa. En breve veremos cómo se arregla esto.

• Hay dos columnas que contienen el nombre de la ciudad. Esto es correcto porque las listas de columnas de la weathery citieslas mesas están concatenadas. En la práctica esto es indeseable, sin embargo, por lo que probablemente querrá enumerar las columnas de salida explícitamente en lugar de usar *:

postgresql SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather JOIN cities ON city = name;

Dado que todas las columnas tenían diferentes nombres, el analizador encontró automáticamente a qué tabla pertenecían. Si hubiera nombres de columnas duplicados en las dos tablas tendrías que calificar los nombres de las columnas para mostrar cuál se refería, como en:

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather JOIN cities ON weather.city = cities.name;

Es ampliamente considerado buen estilo para calificar todos los nombres de columna en una consulta de unión, de modo que la consulta no fallará si un nombre de columna duplicado se añade más tarde a una de las tablas.

Suscrímense a las preguntas del tipo visto hasta ahora también se pueden escribir en esta forma:

SELECT *
    FROM weather, cities
    WHERE city = name;

Esta sintaxis es anterior a la JOIN/ ONsintaxis, que se introdujo en SQL-92. Las tablas se enumeran simplemente en la FROMcláusula, y la expresión de comparación se añade a la WHEREcláusula. Los resultados de esta sintaxis implícita más antigua y de lo más reciente explícito JOIN/ ONSintaxis son idénticas. Pero para un lector de la consulta, la sintaxis explícita hace su significado más fácil de entender: La condición de unión se introduce por su propia palabra clave, mientras que anteriormente la condición se mezclaba en el WHEREcláusula junto con otras condiciones.

Ahora averiguaremos cómo podemos conseguir los discos de Hayward. Lo que queremos que haga la consulta es es escanear el weathermesa y para cada fila para encontrar la coincidencia citiesfilas (s). Si no se encuentra ninguna fila a juego queremos que algunos valores vacíos sean sustituidos por el citieslas columnas de la mesa. Este tipo de consulta se llama unión externa. (Las uniones que hemos visto hasta ahora son uniciones internas.) El comando se ve así:

SELECT *
    FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 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 rows)

Esta consulta se llama unión externa izquierda porque la tabla mencionada a la izquierda del operador de unión tendrá cada una de sus filas en la salida al menos una vez, mientras que la tabla en la derecha sólo tendrá esa salida de filas que coincen con alguna fila de la tabla izquierda. Al salir de una fila de mesa izquierda para la que no hay coincidencia de la mesa derecha, los valores vacíos (null) se sustituyen por las columnas de la mesa derecha.

Ejercicio: También hay uniones exteriores derechas y únete exterior completo. Intenta averiguar qué hacen.

También podemos unirnos a una mesa contra sí misma. Esto se llama unirse a sí mismo. Como ejemplo, supongamos que deseamos encontrar todos los registros meteorológicos que están en el rango de temperatura de otros registros meteorológicos. Así que tenemos que comparar el temp_loy temp_hicolumnas de cada uno weatherfila a la temp_loy temp_hicolumnas de todos los demás weatherfilas. Podemos hacerlo con la siguiente consulta:

SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
       w2.city, w2.temp_lo AS low, w2.temp_hi AS high
    FROM weather w1 JOIN weather w2
        ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;

     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

Aquí hemos reetiquetado la tabla de la meteorología como w1y w2para poder distinguir el lado izquierdo y derecho de la unión. También puede utilizar este tipo de alias en otras consultas para ahorrar algunos mees, por ejemplo:

SELECT *
    FROM weather w JOIN cities c ON w.city = c.name;

Usted encontrará este estilo de abreviatura con bastante frecuencia.