HiveQL Select-Joins



  • Hive Select-Joins

    JOIN是一个子句,用于通过使用每个表的公共值来组合两个表中的特定字段。它用于合并数据库中两个或多个表中的记录。
  • 句法

    
    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]
    
    
    - 在本章中,我们将使用以下两个表。请考虑下表CUSTOMERS。
    
    +----+----------+-----+-----------+----------+ 
    | 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 | 
    +----+----------+-----+-----------+----------+
    
    
    考虑另一个表ORDERS,如下所示:
    
    +-----+---------------------+-------------+--------+ 
    |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   | 
    +-----+---------------------+-------------+--------+
    
    
    给出了不同类型的联接,如下所示:
    • JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • FULL OUTER JOIN
  • JOIN

    JOIN子句用于合并和检索来自多个表的记录。JOIN与SQL中的OUTER JOIN相同。将使用表的主键和外键引发JOIN条件。以下查询在CUSTOMER和ORDER表上执行JOIN,并检索记录:
    
    hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT FROM CUSTOMERS c JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);
    
    
    成功执行查询后,您将看到以下响应:
    
    +----+----------+-----+--------+ 
    | ID | NAME     | AGE | AMOUNT | 
    +----+----------+-----+--------+ 
    | 3  | kaushik  | 23  | 3000   | 
    | 3  | kaushik  | 23  | 1500   | 
    | 2  | Khilan   | 25  | 1560   | 
    | 4  | Chaitali | 25  | 2060   | 
    +----+----------+-----+--------+
    
    
  • LEFT OUTER JOIN

    HiveQL LEFT OUTER JOIN返回左表中的所有行,即使右表中没有匹配项也是如此。这意味着,如果ON子句与右表中的0(零)条记录匹配,则JOIN仍返回结果中的一行,但右表中的每一列都为NULL。
    LEFT JOIN返回左表中的所有值,再加上右表中的匹配值,如果没有匹配的JOIN谓词,则返回NULL。
    以下查询演示了CUSTOMER和ORDER表之间的LEFT OUTER JOIN:
    
    hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE 
    FROM CUSTOMERS c 
    LEFT OUTER JOIN ORDERS o 
    ON (c.ID = o.CUSTOMER_ID);
    
    
    成功执行查询后,您将看到以下响应:
    
    +----+----------+--------+---------------------+ 
    | 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                | 
    +----+----------+--------+---------------------+
    
    
  • RIGHT OUTER JOIN

    即使左表中没有匹配项,HiveQL RIGHT OUTER JOIN也会返回右表中的所有行。如果ON子句与左表中的0(零)记录匹配,则JOIN仍返回结果行,但左表中的每一列都为NULL。
    RIGHT JOIN返回右表中的所有值,再加上左表中的匹配值,如果没有匹配的谓词,则返回NULL。
    以下查询演示了CUSTOMER和ORDER表之间的RIGHT OUTER JOIN。
    
    hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c RIGHT OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);
    
    
    成功执行查询后,您将看到以下响应:
    
    +------+----------+--------+---------------------+ 
    | 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 | 
    +------+----------+--------+---------------------+
    
    
  • FULL OUTER JOIN

    HiveQL FULL OUTER JOIN组合了满足JOIN条件的左右外部表的记录。联接的表包含两个表中的所有记录,或为任一侧缺少的匹配项填充NULL值。
    以下查询演示了CUSTOMER和ORDER表之间的FULL OUTER JOIN:
    
    hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c FULL OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);
    
    
    成功执行查询后,您将看到以下响应:
    
    +------+----------+--------+---------------------+ 
    | 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 | 
    +------+----------+--------+---------------------+