HiveQL - Seleccionar uniones

JOIN es una cláusula que se usa para combinar campos específicos de dos tablas usando valores comunes a cada una. Se utiliza para combinar registros de dos o más tablas en la base de datos.

Sintaxis

join_table:

   table_reference JOIN table_factor [join_condition]
   | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference
   join_condition
   | table_reference LEFT SEMI JOIN table_reference join_condition
   | table_reference CROSS JOIN table_reference [join_condition]

Ejemplo

Usaremos las siguientes dos tablas en este capítulo. Considere la siguiente tabla denominada CLIENTES.

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 2000.00  |  
| 2  | Khilan   | 25  | Delhi     | 1500.00  |  
| 3  | kaushik  | 23  | Kota      | 2000.00  | 
| 4  | Chaitali | 25  | Mumbai    | 6500.00  | 
| 5  | Hardik   | 27  | Bhopal    | 8500.00  | 
| 6  | Komal    | 22  | MP        | 4500.00  | 
| 7  | Muffy    | 24  | Indore    | 10000.00 | 
+----+----------+-----+-----------+----------+

Considere otra tabla PEDIDOS de la siguiente manera:

+-----+---------------------+-------------+--------+ 
|OID  | DATE                | CUSTOMER_ID | AMOUNT | 
+-----+---------------------+-------------+--------+ 
| 102 | 2009-10-08 00:00:00 |           3 | 3000   | 
| 100 | 2009-10-08 00:00:00 |           3 | 1500   | 
| 101 | 2009-11-20 00:00:00 |           2 | 1560   | 
| 103 | 2008-05-20 00:00:00 |           4 | 2060   | 
+-----+---------------------+-------------+--------+

Hay diferentes tipos de combinaciones que se indican a continuación:

• JOIN
• IZQUIERDA COMBINACIÓN EXTERNA
• UNIÓN EXTERIOR DERECHA
• UNIÓN EXTERIOR COMPLETA

UNIRSE

La cláusula JOIN se utiliza para combinar y recuperar los registros de varias tablas. JOIN es lo mismo que OUTER JOIN en SQL. Se debe generar una condición JOIN utilizando las claves principales y las claves externas de las tablas.

La siguiente consulta ejecuta JOIN en las tablas CUSTOMER y ORDER y recupera los registros:

hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT 
FROM CUSTOMERS c JOIN ORDERS o 
ON (c.ID = o.CUSTOMER_ID);

En la ejecución exitosa de la consulta, puede ver la siguiente respuesta:

+----+----------+-----+--------+ 
| ID | NAME     | AGE | AMOUNT | 
+----+----------+-----+--------+ 
| 3  | kaushik  | 23  | 3000   | 
| 3  | kaushik  | 23  | 1500   | 
| 2  | Khilan   | 25  | 1560   | 
| 4  | Chaitali | 25  | 2060   | 
+----+----------+-----+--------+

IZQUIERDA COMBINACIÓN EXTERNA

El HiveQL LEFT OUTER JOIN devuelve todas las filas de la tabla izquierda, incluso si no hay coincidencias en la tabla derecha. Esto significa que, si la cláusula ON coincide con 0 (cero) registros en la tabla de la derecha, JOIN aún devuelve una fila en el resultado, pero con NULL en cada columna de la tabla de la derecha.

A LEFT JOIN devuelve todos los valores de la tabla de la izquierda, más los valores coincidentes de la tabla de la derecha, o NULL en caso de que el predicado JOIN no coincida.

La siguiente consulta demuestra LEFT OUTER JOIN entre las tablas CUSTOMER y ORDER:

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE 
FROM CUSTOMERS c 
LEFT OUTER JOIN ORDERS o 
ON (c.ID = o.CUSTOMER_ID);

En la ejecución exitosa de la consulta, puede ver la siguiente respuesta:

+----+----------+--------+---------------------+ 
| ID | NAME     | AMOUNT | DATE                | 
+----+----------+--------+---------------------+ 
| 1  | Ramesh   | NULL   | NULL                | 
| 2  | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 3  | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3  | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 4  | Chaitali | 2060   | 2008-05-20 00:00:00 | 
| 5  | Hardik   | NULL   | NULL                | 
| 6  | Komal    | NULL   | NULL                | 
| 7  | Muffy    | NULL   | NULL                | 
+----+----------+--------+---------------------+

UNIÓN EXTERIOR DERECHA

HiveQL RIGHT OUTER JOIN devuelve todas las filas de la tabla derecha, incluso si no hay coincidencias en la tabla izquierda. Si la cláusula ON coincide con 0 (cero) registros en la tabla de la izquierda, JOIN todavía devuelve una fila en el resultado, pero con NULL en cada columna de la tabla de la izquierda.

A RIGHT JOIN devuelve todos los valores de la tabla de la derecha, más los valores coincidentes de la tabla de la izquierda, o NULL en caso de que no haya un predicado de unión coincidente.

La siguiente consulta demuestra RIGHT OUTER JOIN entre las tablas CUSTOMER y ORDER.

notranslate "> colmena> SELECCIONAR c.ID, c.NAME, o.AMOUNT, oFECHA DE CLIENTES c ÓRDENES DE UNIÓN EXTERNA DERECHA o ON (c.ID = o.CUSTOMER_ID);

En la ejecución exitosa de la consulta, puede ver la siguiente respuesta:

+------+----------+--------+---------------------+ 
| ID   | NAME     | AMOUNT | DATE                | 
+------+----------+--------+---------------------+ 
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | 
+------+----------+--------+---------------------+

UNIÓN EXTERIOR COMPLETA

HiveQL FULL OUTER JOIN combina los registros de las tablas externas izquierda y derecha que cumplen la condición JOIN. La tabla unida contiene todos los registros de ambas tablas o rellena los valores NULL para las coincidencias que faltan en cualquier lado.

La siguiente consulta demuestra FULL OUTER JOIN entre las tablas CUSTOMER y ORDER:

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE 
FROM CUSTOMERS c 
FULL OUTER JOIN ORDERS o 
ON (c.ID = o.CUSTOMER_ID);

En la ejecución exitosa de la consulta, puede ver la siguiente respuesta:

+------+----------+--------+---------------------+ 
| ID   | NAME     | AMOUNT | DATE                | 
+------+----------+--------+---------------------+ 
| 1    | Ramesh   | NULL   | NULL                | 
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | 
| 5    | Hardik   | NULL   | NULL                | 
| 6    | Komal    | NULL   | NULL                |
| 7    | Muffy    | NULL   | NULL                |  
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | 
+------+----------+--------+---------------------+