PostgreSQL Combinaciones (JOIN)
La cláusula JOIN de PostgreSQL se usa para combinar filas de dos o más tablas, basándose en los campos comunes entre estas tablas.
En PostgreSQL, JOIN tiene cinco tipos de combinación:
- CROSS JOIN: combinación cruzada
- INNER JOIN: combinación interna
- LEFT OUTER JOIN: combinación externa izquierda
- RIGHT OUTER JOIN: combinación externa derecha
- FULL OUTER JOIN: combinación externa completa
A continuación, creemos dos tablas.COMPANYyDEPARTMENT。
Ejemplo
Crear la tabla COMPANY (Descargar el archivo SQL de COMPANY), cuyos datos son los siguientes:
ejemplodb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
Agreguemos algunos registros a la tabla:
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00); INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00); INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
En este momento, los registros de la tabla COMPANY son los siguientes:
id | name | age | address | salary ----+-------+-----+--------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows)
Cree una tabla DEPARTMENT, agregando tres campos:
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
Inserte tres registros en la tabla DEPARTMENT:
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
En este momento, los registros de la tabla DEPARTMENT son los siguientes:
id | dept | emp_id ----+-------------+-------- 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7
Combinación cruzada
La combinación cruzada (CROSS JOIN) empareja cada fila de la primera tabla con cada fila de la segunda tabla. Si las dos tablas de entrada tienen x e y filas respectivamente, la tabla resultante tendrá x*y filas.
Dado que la combinación cruzada (CROSS JOIN) puede producir tablas muy grandes, se debe usar con precaución y solo cuando sea apropiado.
A continuación se muestra la sintaxis básica de CROSS JOIN:
SELECT ... FROM table1 CROSS JOIN table2 ...
Basándonos en las tablas anteriores, podemos escribir una combinación cruzada (CROSS JOIN), como se muestra a continuación:
ejemplodb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
Obtenemos el siguiente resultado:
ejemplodb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
emp_id | name | dept
--------+-------+--------------------
1 | Paul | IT Billing
1 | Allen | IT Billing
1 | Teddy | IT Billing
1 | Mark | IT Billing
1 | David | IT Billing
1 | Kim | IT Billing
1 | James | IT Billing
1 | Paul | IT Billing
1 | James | IT Billing
1 | James | IT Billing
2 | Paul | Engineering
2 | Allen | Engineering
2 | Teddy | Engineering
2 | Mark | Engineering
2 | David | Engineering
2 | Kim | Engineering
2 | James | Engineering
2 | Paul | Engineering
2 | James | Engineering
2 | James | Engineering
7 | Paul | Finance
Combinación interna
La combinación interna (INNER JOIN) crea una nueva tabla de resultados combinando los valores de las columnas de dos tablas (table1 y table2) según el predicado de combinación. La consulta compara cada fila de table1 con cada fila de table2 y encuentra todos los pares de filas que cumplen el predicado de combinación.
Cuando se cumple el predicado de combinación, los valores de las columnas de cada par coincidente de filas A y B se fusionan en una fila de resultado.
La combinación interna (INNER JOIN) es el tipo de combinación más común y es el tipo de combinación predeterminado.
La palabra clave INNER es opcional.
A continuación se muestra la sintaxis de la combinación interna (INNER JOIN):
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field;
Basándonos en las tablas anteriores, podemos escribir una combinación interna, como se muestra a continuación:
ejemplodb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+--------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 rows)
Combinación externa izquierda
La combinación externa es una extensión de la combinación interna. El estándar SQL define tres tipos de combinaciones externas: LEFT, RIGHT y FULL; PostgreSQL admite todas ellas.
Para la combinación externa izquierda, primero se ejecuta una combinación interna. Luego, para cada fila en la tabla T1 que no cumple la condición de combinación con la tabla T2, se agrega una fila de combinación con valores null en las columnas de T2. Por lo tanto, la tabla combinada tiene al menos una fila por cada fila de T1.
A continuación se muestra la sintaxis básica de la combinación externa izquierda (LEFT OUTER JOIN):
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
Basándonos en las dos tablas anteriores, podemos escribir una combinación externa izquierda, como se muestra a continuación:
ejemplodb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 rows)
Combinación externa derecha
Primero, se ejecuta la combinación interna. Luego, para cada fila de la tabla T2 que no cumple la condición de combinación con la tabla T1, se agrega una fila de combinación con valores null en las columnas de T1. Esto es lo opuesto a la combinación izquierda; para cada fila de T2, la tabla de resultados siempre tiene una fila.
A continuación se muestra la sintaxis básica de la combinación externa derecha (RIGHT OUTER JOIN):
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
Basándonos en las dos tablas anteriores, creamos una combinación externa derecha:
ejemplodb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+-----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 rows)
Combinación externa
Primero, se ejecuta la combinación interna. Luego, para cada fila de la tabla T1 que no cumple la condición de combinación con ninguna fila de la tabla T2, se agrega al resultado una fila con valores null en las columnas de T2. Además, para cada fila de T2 que no cumple la condición de combinación con ninguna fila de T1, se agrega al resultado una fila con valores null en las columnas de T1.
A continuación se muestra la sintaxis básica de la combinación externa:
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
Basándonos en las dos tablas anteriores, podemos crear una combinación externa:
ejemplodb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+-----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 rows)
Otras extensiones