Hasta ahora, nuestras consultas solo han accedido a una tabla a la vez. Las consultas pueden acceder a múltiples tablas a la vez, o acceder a la misma tabla de tal manera que se procesen varias filas de la tabla al mismo tiempo. Las consultas que acceden a múltiples tablas (o a múltiples instancias de la misma tabla) a la vez se denominan consultas de unión o join. Estas combinan filas de una tabla con filas de una segunda tabla, con una expresión que especifica qué filas se deben emparejar. Por ejemplo, para devolver todos los registros meteorológicos junto con la ubicación de la ciudad asociada, la base de datos debe comparar la columna city de cada fila de la tabla weather con la columna name de todas las filas de la tabla cities, y seleccionar los pares de filas donde estos valores coincidan.^[4] 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)

Observa dos cosas sobre el conjunto de resultados:

Dado que todas las columnas tenían nombres diferentes, el analizador (parser) encontró automáticamente a qué tabla pertenecen. Si hubiera nombres de columna duplicados en las dos tablas, tendrías que calificar (qualify) los nombres de las columnas para mostrar a cuál te refieres, 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;

Se considera ampliamente un buen estilo calificar todos los nombres de columna en una consulta de unión, de modo que la consulta no falle si más adelante se añade un nombre de columna duplicado a una de las tablas.

Las consultas de unión del tipo visto hasta ahora también se pueden escribir de esta forma:

SELECT *
    FROM weather, cities
    WHERE city = name;

Esta sintaxis es anterior a la sintaxis JOIN/ON, que se introdujo en SQL-92. Las tablas simplemente se listan en la cláusula FROM, y la expresión de comparación se añade a la cláusula WHERE. Los resultados de esta sintaxis implícita más antigua y de la más nueva sintaxis explícita JOIN/ON son idénticos. Pero para un lector de la consulta, la sintaxis explícita hace que su significado sea más fácil de entender: la condición de unión se introduce mediante su propia palabra clave, mientras que antes la condición se mezclaba en la cláusula WHERE junto con otras condiciones.

Now we will figure out how we can get the Hayward records back in. What we want the query to do is to scan the weather table and for each row to find the matching cities row(s). If no matching row is found we want some “empty values” to be substituted for the cities table's columns. This kind of query is called an outer join. (The joins we have seen so far are inner joins.) 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 denomina unión externa izquierda o left outer join 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 de la derecha solo tendrá aquellas filas de salida que coincidan con alguna fila de la tabla izquierda. Al emitir una fila de la tabla izquierda para la cual no hay coincidencia en la tabla derecha, se sustituyen por valores vacíos (null) las columnas de la tabla derecha.

Ejercicio:  También existen las uniones externas derechas (right outer joins) y las uniones externas completas (full outer joins). Intenta averiguar qué hacen esas.

También podemos unir una tabla consigo misma. Esto se denomina autounión o self join. Como ejemplo, supongamos que deseamos encontrar todos los registros meteorológicos que se encuentran en el rango de temperatura de otros registros meteorológicos. Por lo tanto, necesitamos comparar las columnas temp_lo y temp_hi de cada fila de la tabla weather con las columnas temp_lo y temp_hi de todas las demás filas de la tabla weather. Podemos hacer esto 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 renombrado la tabla weather como w1 y w2 para poder distinguir el lado izquierdo y el derecho de la unión. También puedes utilizar este tipo de alias en otras consultas para ahorrar algo de escritura, por ejemplo:

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

Te encontrarás con este estilo de abreviatura con bastante frecuencia.