skip to main content
SQL Functionality for the Driver for Apache Hive : From Clause
  

Try DataDirect Drivers Now

From Clause

LEFT, RIGHT, and FULL OUTER JOINs are supported, as are LEFT SEMI JOINs and CROSS JOINs using the equal comparison operator, as shown in the following examples
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON
(c.key = b.key2)

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'
However, the following syntax fails because of the use of non-equal comparison operators.
SELECT a.* FROM a JOIN b ON (a.id <> b.id)
HiveQL does not support join syntax in the form of a comma-separated list of tables. The driver, however, overcomes this limitation by translating the SQL into HiveQL, as shown in the following examples.
ANSI SQL 92 Query
Driver for Apache Hive Wire HiveQL Translation
SELECT * FROM t1, t2 WHERE a = b
SELECT * FROM t1 t1 JOIN t2 t2 WHERE a = b
SELECT * FROM t1 y, t2 x WHERE a = b
SELECT * FROM t1 y JOIN t2 x WHERE a = b
SELECT * FROM t2, (SELECT * FROM t1) x
SELECT * FROM t2 t2 JOIN (SELECT * FROM t1 t1) x