01.-Joins

PostgreSQL join se utiliza para combinar columnas de una self-join o más tablas basándose en los valores de las columnas comunes entre las tablas relacionadas. Las columnas comunes suelen ser las columnas de clave primaria de la primera tabla y las columnas de clave externa de la segunda tabla.

PostgreSQL soporta inner join, left join, right join, full outer join, cross join, natural join, y un tipo especial de join llamado self-join.

Configuración de tablas de ejemplo¶

Suponga que tiene dos tablas llamadas cesta_a y cesta_b que almacenan frutas:

CREATE TABLE basket_a (
    a INT PRIMARY KEY,
    fruit_a VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (
    b INT PRIMARY KEY,
    fruit_b VARCHAR (100) NOT NULL
);

INSERT INTO basket_a (a, fruit_a)
VALUES
    (1, 'Apple'),
    (2, 'Orange'),
    (3, 'Banana'),
    (4, 'Cucumber');

INSERT INTO basket_b (b, fruit_b)
VALUES
    (1, 'Orange'),
    (2, 'Apple'),
    (3, 'Watermelon'),
    (4, 'Pear');

Las tablas tienen algunas frutas comunes como manzana y naranja.

La siguiente sentencia devuelve datos de la tabla basket_a:

 a | fruit_a
---+----------
 1 | Apple
 2 | Orange
 3 | Banana
 4 | Cucumber
(4 rows)

La siguiente sentencia devuelve datos de la tabla basket_b:

 b |  fruit_b
---+------------
 1 | Orange
 2 | Apple
 3 | Watermelon
 4 | Pear
(4 rows)

Unión interna PostgreSQL¶

La siguiente sentencia une la primera tabla (basket_a) con la segunda tabla (basket_b) comparando los valores de las columnas fruit_a y fruit_b:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
INNER JOIN basket_b
    ON fruit_a = fruit_b;

Salida:

 a | fruit_a | b | fruit_b
---+---------+---+---------
 1 | Apple   | 2 | Apple
 2 | Orange  | 1 | Orange
(2 rows)

La unión interna examina cada fila de la primera tabla (cesta_a). Compara el valor de la columna fruta_a con el valor de la columna fruta_b de cada fila de la segunda tabla (cesta_b). Si estos valores son iguales, la unión interna crea una nueva fila que contiene columnas de ambas tablas y añade esta nueva fila al conjunto de resultados.

El siguiente diagrama de Venn ilustra la unión interna:

PostgreSQL Join - Inner Join

PostgreSQL left join¶

La siguiente sentencia utiliza la cláusula left join para unir la tabla basket_a con la tabla basket_b. En el contexto de left join, la primera tabla se denomina tabla izquierda y la segunda tabla se denomina tabla derecha.

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b 
   ON fruit_a = fruit_b;

Salida:

 a | fruit_a  |  b   | fruit_b
---+----------+------+---------
 1 | Apple    |    2 | Apple
 2 | Orange   |    1 | Orange
 3 | Banana   | null | null
 4 | Cucumber | null | null
(4 rows)Code language: JavaScript (javascript)

La unión izquierda comienza seleccionando datos de la tabla izquierda. Compara los valores de la columna fruta_a con los valores de la columna fruta_b de la tabla cesta_b.

Si estos valores son iguales, la unión izquierda crea una nueva fila que contiene columnas de ambas tablas y añade esta nueva fila al conjunto de resultados. (véase la fila nº 1 y nº 2 en el conjunto de resultados).

En caso de que los valores no sean iguales, la unión izquierda también crea una nueva fila que contiene columnas de ambas tablas y la añade al conjunto de resultados. Sin embargo, rellena las columnas de la tabla derecha (basket_b) con null. (véanse las filas nº 3 y 4 del conjunto de resultados).

El siguiente diagrama de Venn ilustra la unión izquierda:

PostgreSQL Join - Left Join

Para seleccionar filas de la tabla izquierda que no tienen filas coincidentes en la tabla derecha, utilice la unión izquierda con una cláusula WHERE. Por ejemplo:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b 
    ON fruit_a = fruit_b
WHERE b IS NULL;

La salida es:

 a | fruit_a  |  b   | fruit_b
---+----------+------+---------
 3 | Banana   | null | null
 4 | Cucumber | null | null
(2 rows)
Code language: JavaScript (javascript)

Tenga en cuenta que el LEFT JOIN es lo mismo que el LEFT OUTER JOIN, por lo que puede utilizarlos indistintamente.

El siguiente diagrama de Venn ilustra la unión izquierda que devuelve filas de la tabla izquierda que no tienen filas coincidentes de la tabla derecha:

PostgreSQL Join - Left Join with Where

PostgreSQL right join¶

El right join es una versión invertida del left join. El right join comienza seleccionando datos de la tabla derecha. Compara cada valor de la columna fruit_b de cada fila de la tabla derecha con cada valor de la columna fruit_a de cada fila de la tabla fruit_a.

Si estos valores son iguales, la unión a la derecha crea una nueva fila que contiene columnas de ambas tablas.

En caso de que estos valores no sean iguales, la unión a la derecha también crea una nueva fila que contiene columnas de ambas tablas. Sin embargo, rellena las columnas de la tabla izquierda con NULL.

La siguiente sentencia utiliza la unión a la derecha para unir la tabla cesta_a con la tabla cesta_b:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b ON fruit_a = fruit_b;

Here is the output:

  a   | fruit_a | b |  fruit_b
------+---------+---+------------
    2 | Orange  | 1 | Orange
    1 | Apple   | 2 | Apple
 null | null    | 3 | Watermelon
 null | null    | 4 | Pear
(4 rows)Code language: JavaScript (javascript)

The following Venn diagram illustrates the right join:

PostgreSQL Join - Right Join

Similarly, you can get rows from the right table that do not have matching rows from the left table by adding a WHERE clause as follows:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b 
   ON fruit_a = fruit_b
WHERE a IS NULL;

Salida:

  a   | fruit_a | b |  fruit_b
------+---------+---+------------
 null | null    | 3 | Watermelon
 null | null    | 4 | Pear
(2 rows)Code language: JavaScript (javascript)

The RIGHT JOIN and RIGHT OUTER JOIN are the same therefore you can use them interchangeably.

The following Venn diagram illustrates the right join that returns rows from the right table that do not have matching rows in the left table:

PostgreSQL Join - Right Join with Where

PostgreSQL full outer join¶

El full outer join o full join devuelve un conjunto de resultados que contiene todas las filas de las tablas izquierda y derecha, con las filas coincidentes de ambos lados si están disponibles. En caso de que no haya ninguna coincidencia, las columnas de la tabla se rellenarán con NULL.

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL OUTER JOIN basket_b 
    ON fruit_a = fruit_b;

Salida:

  a   | fruit_a  |  b   |  fruit_b
------+----------+------+------------
    1 | Apple    |    2 | Apple
    2 | Orange   |    1 | Orange
    3 | Banana   | null | null
    4 | Cucumber | null | null
 null | null     |    3 | Watermelon
 null | null     |    4 | Pear
(6 rows)
Code language: JavaScript (javascript)

El siguiente diagrama de Venn ilustra la unión externa completa:

PostgreSQL Join - Full Outer Join

Para devolver filas en una tabla que no tienen filas coincidentes en la otra, se utiliza la unión completa con una cláusula WHERE como ésta:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL JOIN basket_b 
   ON fruit_a = fruit_b
WHERE a IS NULL OR b IS NULL;

He aquí el resultado:

  a   | fruit_a  |  b   |  fruit_b
------+----------+------+------------
    3 | Banana   | null | null
    4 | Cucumber | null | null
 null | null     |    3 | Watermelon
 null | null     |    4 | Pear
(4 rows)Code language: JavaScript (javascript)

El siguiente diagrama de Venn ilustra la unión externa completa que devuelve filas de una tabla que no tienen las filas correspondientes en la otra tabla:

PostgreSQL Join - Full Outer Join with Where

La siguiente imagen muestra todas las uniones PostgreSQL que hemos discutido hasta ahora con la sintaxis detallada:

PostgreSQL Joins

La siguiente imagen muestra todas las uniones PostgreSQL que hemos discutido hasta ahora con la sintaxis detallada:

CREATE TABLE basket_a (
    a INT PRIMARY KEY,
    fruit_a VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (
    b INT PRIMARY KEY,
    fruit_b VARCHAR (100) NOT NULL
);

INSERT INTO basket_a (a, fruit_a)
VALUES
    (1, 'Apple'),
    (2, 'Orange'),
    (3, 'Banana'),
    (4, 'Cucumber');

INSERT INTO basket_b (b, fruit_b)
VALUES
    (1, 'Orange'),
    (2, 'Apple'),
    (3, 'Watermelon'),
    (4, 'Pear');

Las tablas tienen algunas frutas comunes como manzana y naranja.

La siguiente sentencia devuelve datos de la tabla basket_a:

 a | fruit_a
---+----------
 1 | Apple
 2 | Orange
 3 | Banana
 4 | Cucumber
(4 rows)

La siguiente sentencia devuelve datos de la tabla basket_b:

 b |  fruit_b
---+------------
 1 | Orange
 2 | Apple
 3 | Watermelon
 4 | Pear
(4 rows)

PostgreSQL inner join¶

La siguiente sentencia une la primera tabla (cesta_a) con la segunda tabla (cesta_b) comparando los valores de las columnas fruta_a y fruta_b:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
INNER JOIN basket_b
    ON fruit_a = fruit_b;

Salida:

 a | fruit_a | b | fruit_b
---+---------+---+---------
 1 | Apple   | 2 | Apple
 2 | Orange  | 1 | Orange
(2 rows)

La unión interna examina cada fila de la primera tabla (cesta_a). Compara el valor de la columna fruta_a con el valor de la columna fruta_b de cada fila de la segunda tabla (cesta_b). Si estos valores son iguales, la unión interna crea una nueva fila que contiene columnas de ambas tablas y añade esta nueva fila al conjunto de resultados.

El siguiente diagrama de Venn ilustra la unión interna:

PostgreSQL Join - Inner Join

PostgreSQL left join¶

La siguiente sentencia utiliza la cláusula left join para unir la tabla basket_a con la tabla basket_b. En el contexto de left join, la primera tabla se denomina tabla izquierda y la segunda tabla se denomina tabla derecha.

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b 
   ON fruit_a = fruit_b;

Salida:

 a | fruit_a  |  b   | fruit_b
---+----------+------+---------
 1 | Apple    |    2 | Apple
 2 | Orange   |    1 | Orange
 3 | Banana   | null | null
 4 | Cucumber | null | null
(4 rows)Code language: JavaScript (javascript)

La unión izquierda comienza seleccionando datos de la tabla izquierda. Compara los valores de la columna fruta_a con los valores de la columna fruta_b de la tabla cesta_b.

Si estos valores son iguales, la unión izquierda crea una nueva fila que contiene columnas de ambas tablas y añade esta nueva fila al conjunto de resultados. (véase la fila nº 1 y nº 2 en el conjunto de resultados).

En caso de que los valores no sean iguales, la unión izquierda también crea una nueva fila que contiene columnas de ambas tablas y la añade al conjunto de resultados. Sin embargo, rellena las columnas de la tabla derecha (basket_b) con null. (véanse las filas nº 3 y 4 del conjunto de resultados).

El siguiente diagrama de Venn ilustra la unión izquierda:

PostgreSQL Join - Left Join

Para seleccionar filas de la tabla izquierda que no tienen filas coincidentes en la tabla derecha, utilice la unión izquierda con una cláusula WHERE. Por ejemplo:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b 
    ON fruit_a = fruit_b
WHERE b IS NULL;

La salida es:

 a | fruit_a  |  b   | fruit_b
---+----------+------+---------
 3 | Banana   | null | null
 4 | Cucumber | null | null
(2 rows)

Tenga en cuenta que el LEFT JOIN es lo mismo que el LEFT OUTER JOIN, por lo que puede utilizarlos indistintamente.

El siguiente diagrama de Venn ilustra la unión izquierda que devuelve filas de la tabla izquierda que no tienen filas coincidentes de la tabla derecha:

PostgreSQL Join - Left Join with Where

PostgreSQL right join¶

El right join es una versión invertida del left join. El right join comienza seleccionando datos de la tabla derecha. Compara cada valor de la columna fruit_b de cada fila de la tabla derecha con cada valor de la columna fruit_a de cada fila de la tabla fruit_a.

Si estos valores son iguales, la unión a la derecha crea una nueva fila que contiene columnas de ambas tablas.

En caso de que estos valores no sean iguales, la unión a la derecha también crea una nueva fila que contiene columnas de ambas tablas. Sin embargo, rellena las columnas de la tabla izquierda con NULL.

La siguiente sentencia utiliza la unión a la derecha para unir la tabla cesta_a con la tabla cesta_b:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b ON fruit_a = fruit_b;

Este es el resultado:

  a   | fruit_a | b |  fruit_b
------+---------+---+------------
    2 | Orange  | 1 | Orange
    1 | Apple   | 2 | Apple
 null | null    | 3 | Watermelon
 null | null    | 4 | Pear
(4 rows)Code language: JavaScript (javascript)

El siguiente diagrama de Venn ilustra la unión correcta:

PostgreSQL Join - Right Join

Del mismo modo, puede obtener filas de la tabla derecha que no tengan filas coincidentes de la tabla izquierda añadiendo una cláusula WHERE como se indica a continuación:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b 
   ON fruit_a = fruit_b
WHERE a IS NULL;

Salida:

  a   | fruit_a | b |  fruit_b
------+---------+---+------------
 null | null    | 3 | Watermelon
 null | null    | 4 | Pear
(2 rows)Code language: JavaScript (javascript)

El RIGHT JOIN y el RIGHT OUTER JOIN son lo mismo, por lo que puede utilizarlos indistintamente.

El siguiente diagrama de Venn ilustra la unión derecha que devuelve filas de la tabla derecha que no tienen filas coincidentes en la tabla izquierda:

PostgreSQL Join - Right Join with Where

PostgreSQL full outer join¶

El full outer join o full join devuelve un conjunto de resultados que contiene todas las filas de las tablas izquierda y derecha, con las filas coincidentes de ambos lados si están disponibles. En caso de que no haya ninguna coincidencia, las columnas de la tabla se rellenarán con NULL.

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL OUTER JOIN basket_b 
    ON fruit_a = fruit_b;

Salida:

  a   | fruit_a  |  b   |  fruit_b
------+----------+------+------------
    1 | Apple    |    2 | Apple
    2 | Orange   |    1 | Orange
    3 | Banana   | null | null
    4 | Cucumber | null | null
 null | null     |    3 | Watermelon
 null | null     |    4 | Pear
(6 rows)
Code language: JavaScript (javascript)

The following Venn diagram illustrates the full outer join:

PostgreSQL Join - Full Outer Join

Para devolver filas en una tabla que no tienen filas coincidentes en la otra, se utiliza la unión completa con una cláusula WHERE como ésta:

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL JOIN basket_b 
   ON fruit_a = fruit_b
WHERE a IS NULL OR b IS NULL;

He aquí el resultado:

  a   | fruit_a  |  b   |  fruit_b
------+----------+------+------------
    3 | Banana   | null | null
    4 | Cucumber | null | null
 null | null     |    3 | Watermelon
 null | null     |    4 | Pear
(4 rows)Code language: JavaScript (javascript)

El siguiente diagrama de Venn ilustra la unión externa completa que devuelve filas de una tabla que no tienen las filas correspondientes en la otra tabla:

PostgreSQL Join - Full Outer Join with Where

La siguiente imagen muestra todas las uniones PostgreSQL que hemos discutido hasta ahora con la sintaxis detallada: PostgreSQL Joins

En este tutorial, ha aprendido a utilizar varios tipos de uniones PostgreSQL para combinar datos de múltiples tablas relacionadas.