{"id":10426,"date":"2018-03-09T11:52:01","date_gmt":"2018-03-09T11:52:01","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=10426"},"modified":"2018-03-09T11:52:01","modified_gmt":"2018-03-09T11:52:01","slug":"hive-join","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/hive-join\/","title":{"rendered":"Hive Join | HiveQL Select Joins Query | Types of Join in Hive"},"content":{"rendered":"<p><span style=\"font-weight: 400\">In <strong>Apache Hive<\/strong>, for combining specific fields from two tables by using values common to each one we use Hive Join &#8211; HiveQL Select Joins Query. However, we need to know the syntax of Hive Join for implementation purpose. <\/span><\/p>\n<p><span style=\"font-weight: 400\">So, in this article, \u201cHive Join &#8211; HiveQL Select Joins Query and its types\u201d we will cover syntax of joins in hive. Also, we will learn an example of Hive Join to understand well. <\/span><\/p>\n<p><span style=\"font-weight: 400\">Moreover, there are several types of Hive join &#8211; HiveQL Select Joins: Hive inner join, hive left outer join, hive right outer join, and hive full outer join. We will also learn Hive Join tables in depth.<\/span><\/p>\n<h2><span style=\"font-weight: 400\">Apache Hive Join &#8211; HiveQL Select Joins Query<\/span><\/h2>\n<p><span style=\"font-weight: 400\">Basically, for combining specific fields from two tables by using values common to each one we use Hive JOIN clause. <\/span><\/p>\n<p><span style=\"font-weight: 400\">In other words, to combine records from two or more tables in the database we use JOIN clause. However, it is more or less similar to SQL JOIN. Also, we use it to combine rows from multiple tables.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Moreover, there are some points we need \u00a0to observe about Hive Join:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"> In Joins, only Equality joins are allowed.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">However, in the same query more than two tables can be joined.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Basically, to offer more control over ON Clause for which there is no match LEFT, RIGHT, FULL OUTER joins exist in order. <\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Also, note that Hive Joins are not Commutative<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Whether they are LEFT or RIGHT joins in Hive, even then Joins are left-associative.<\/span><\/li>\n<\/ul>\n<h3><span style=\"font-weight: 400\">a. Apache Hive Join Syntax<\/span><\/h3>\n<p>Following is a syntax of\u00a0<span style=\"font-weight: 400\">Hive Join &#8211; HiveQL Select Clause.<\/span><br \/>\n<span style=\"font-weight: 400\">join_table:<\/span><br \/>\n<span style=\"font-weight: 400\"> \u00a0\u00a0table_reference JOIN table_factor [join_condition]<\/span><br \/>\n<span style=\"font-weight: 400\"> \u00a0\u00a0| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference<\/span><br \/>\n<span style=\"font-weight: 400\"> \u00a0\u00a0join_condition<\/span><br \/>\n<span style=\"font-weight: 400\"> \u00a0\u00a0| table_reference LEFT SEMI JOIN table_reference join_condition<\/span><br \/>\n<span style=\"font-weight: 400\"> \u00a0\u00a0| table_reference CROSS JOIN table_reference [join_condition]<\/span><\/p>\n<h3><span style=\"font-weight: 400\">b. Example of Join in Hive<\/span><\/h3>\n<p>Example of\u00a0<span style=\"font-weight: 400\">Hive Join &#8211; HiveQL Select Clause.<\/span><br \/>\n<span style=\"font-weight: 400\">However, to understand well let\u2019s suppose the following table named CUSTOMERS.<\/span><br \/>\n<strong>Table.1 Hive Join Example<\/strong><\/p>\n<table>\n<tbody>\n<tr>\n<td><strong>ID<\/strong><\/td>\n<td><strong>Name<\/strong><\/td>\n<td><strong>Age<\/strong><\/td>\n<td><strong>Address<\/strong><\/td>\n<td><strong>Salary<\/strong><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">1<\/span><\/td>\n<td><span style=\"font-weight: 400\">Ross<\/span><\/td>\n<td><span style=\"font-weight: 400\">32<\/span><\/td>\n<td><span style=\"font-weight: 400\">Ahmedabad<\/span><\/td>\n<td><span style=\"font-weight: 400\">2000<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">2<\/span><\/td>\n<td><span style=\"font-weight: 400\">Rachel<\/span><\/td>\n<td><span style=\"font-weight: 400\">25<\/span><\/td>\n<td><span style=\"font-weight: 400\">Delhi<\/span><\/td>\n<td><span style=\"font-weight: 400\">1500<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">23<\/span><\/td>\n<td><span style=\"font-weight: 400\">Kota<\/span><\/td>\n<td><span style=\"font-weight: 400\">2000<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">4<\/span><\/td>\n<td><span style=\"font-weight: 400\">Monika<\/span><\/td>\n<td><span style=\"font-weight: 400\">25<\/span><\/td>\n<td><span style=\"font-weight: 400\">Mumbai<\/span><\/td>\n<td><span style=\"font-weight: 400\">6500<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">5<\/span><\/td>\n<td><span style=\"font-weight: 400\">Mike<\/span><\/td>\n<td><span style=\"font-weight: 400\">27<\/span><\/td>\n<td><span style=\"font-weight: 400\">Bhopal<\/span><\/td>\n<td><span style=\"font-weight: 400\">8500<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">6<\/span><\/td>\n<td><span style=\"font-weight: 400\">Phoebe<\/span><\/td>\n<td><span style=\"font-weight: 400\">22<\/span><\/td>\n<td><span style=\"font-weight: 400\">MP<\/span><\/td>\n<td><span style=\"font-weight: 400\">4500<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">7<\/span><\/td>\n<td><span style=\"font-weight: 400\">Joey<\/span><\/td>\n<td><span style=\"font-weight: 400\">24<\/span><\/td>\n<td><span style=\"font-weight: 400\">Indore<\/span><\/td>\n<td><span style=\"font-weight: 400\">10000<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Also, suppose another table ORDERS as follows:<br \/>\n<strong>Table.2 &#8211; Hive Join<\/strong>\u00a0<strong>Example<\/strong><\/p>\n<table>\n<tbody>\n<tr>\n<td><strong>OID<\/strong><\/td>\n<td><strong>Date<\/strong><\/td>\n<td><strong>Customer_ID<\/strong><\/td>\n<td><strong>Amount<\/strong><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">102<\/span><\/td>\n<td><span style=\"font-weight: 400\"> 2016-10-08 00:00:00<\/span><\/td>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">3000<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">100<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-10-08 00:00:00<\/span><\/td>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">1500<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">101<\/span><\/td>\n<td><span style=\"font-weight: 400\"> 2016-11-20 00:00:00<\/span><\/td>\n<td><span style=\"font-weight: 400\">2<\/span><\/td>\n<td><span style=\"font-weight: 400\">1560<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">103<\/span><\/td>\n<td><span style=\"font-weight: 400\">2015-05-20 00:00:00<\/span><\/td>\n<td><span style=\"font-weight: 400\">4<\/span><\/td>\n<td><span style=\"font-weight: 400\">2060<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>Type of Joins in Hive<\/h2>\n<p><span style=\"font-weight: 400\">Basically, there are 4 types of Hive Join. Such as:<\/span><\/p>\n<div id=\"attachment_10429\" style=\"width: 1210px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Types-of-Hive-joins.jpg\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-10429\" class=\"wp-image-10429 size-full\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Types-of-Hive-joins.jpg\" alt=\"HiveQL Select Joins\" width=\"1200\" height=\"628\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Types-of-Hive-joins.jpg 1200w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Types-of-Hive-joins-150x79.jpg 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Types-of-Hive-joins-300x157.jpg 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Types-of-Hive-joins-768x402.jpg 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Types-of-Hive-joins-1024x536.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/a><p id=\"caption-attachment-10429\" class=\"wp-caption-text\">Types of Hive Join | HiveQL Select Clause<\/p><\/div>\n<ol>\n<li><span style=\"font-weight: 400\"> Inner join in Hive<\/span><\/li>\n<li><span style=\"font-weight: 400\"> Left Outer Join in Hive\u00a0<\/span><\/li>\n<li>Right Outer Join in Hive<\/li>\n<li><span style=\"font-weight: 400\">Full Outer Join in Hive\u00a0<\/span><\/li>\n<\/ol>\n<p><span style=\"font-weight: 400\">So, let\u2019s discuss each Hive join in detail.<\/span><\/p>\n<h3><span style=\"font-weight: 400\">a. Inner Join<\/span><\/h3>\n<p><span style=\"font-weight: 400\">Basically, to combine and retrieve the records from multiple tables we use Hive Join clause. Moreover, in SQL JOIN is as same as OUTER JOIN. Moreover, by using the primary keys and foreign keys of the tables JOIN condition is to be raised.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Furthermore, the below query executes JOIN the CUSTOMER and ORDER tables. Then further retrieves the records:<\/span><br \/>\n<span style=\"font-weight: 400\">hive&gt; SELECT c.ID, c.NAME, c.AGE, o.AMOUNT <\/span><br \/>\n<span style=\"font-weight: 400\">FROM CUSTOMERS c JOIN ORDERS o <\/span><br \/>\n<span style=\"font-weight: 400\">ON (c.ID = o.CUSTOMER_ID);<\/span><\/p>\n<p><span style=\"font-weight: 400\">Moreover, we get to see the following response, on the successful execution of the query:<\/span><\/p>\n<p><strong>Table.3 &#8211; Inner Join in Hive<\/strong><\/p>\n<table>\n<tbody>\n<tr>\n<td><strong>ID<\/strong><\/td>\n<td><strong>Name<\/strong><\/td>\n<td><strong>Age<\/strong><\/td>\n<td><strong>Amount<\/strong><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">23<\/span><\/td>\n<td><span style=\"font-weight: 400\">1300<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">23<\/span><\/td>\n<td><span style=\"font-weight: 400\">1500<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">2<\/span><\/td>\n<td><span style=\"font-weight: 400\">Rachel<\/span><\/td>\n<td><span style=\"font-weight: 400\">25<\/span><\/td>\n<td><span style=\"font-weight: 400\">1560<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">4<\/span><\/td>\n<td><span style=\"font-weight: 400\">Monika<\/span><\/td>\n<td><span style=\"font-weight: 400\">25<\/span><\/td>\n<td><span style=\"font-weight: 400\">2060<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><span style=\"font-weight: 400\">b. Left Outer Join<\/span><\/h3>\n<p><span style=\"font-weight: 400\">On defining HiveQL Left Outer Join, even if there are no matches in the right table it returns all the rows from the left table. <\/span><\/p>\n<p><span style=\"font-weight: 400\">To be more specific, even if the ON clause matches 0 (zero) records in the right table, then also this Hive JOIN still returns a row in the result. Although, it returns with NULL in each column from the right table.<\/span><\/p>\n<p><span style=\"font-weight: 400\">In addition, it returns all the values from the left table. Also, the matched values from the right table, or NULL in case of no matching JOIN predicate.<\/span><br \/>\n<span style=\"font-weight: 400\">However, the below query shows LEFT OUTER JOIN between CUSTOMER as well as ORDER tables:<\/span><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">hive&gt; SELECT c.ID, c.NAME, o.AMOUNT, o.DATE\nFROM CUSTOMERS c\nLEFT OUTER JOIN ORDERS o\nON (c.ID = o.CUSTOMER_ID);<\/pre>\n<p><span style=\"font-weight: 400\">Moreover, we get to see the following response, on the successful execution of the HiveQL Select query:<\/span><\/p>\n<p><strong>Table.4 &#8211; Left Outer Join in Hive<\/strong><\/p>\n<table>\n<tbody>\n<tr>\n<td><strong>ID<\/strong><\/td>\n<td><strong>Name<\/strong><\/td>\n<td><strong>Amount <\/strong><\/td>\n<td><strong>Date<\/strong><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">1<\/span><\/td>\n<td><span style=\"font-weight: 400\">Ross<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">2<\/span><\/td>\n<td><span style=\"font-weight: 400\">Rachel<\/span><\/td>\n<td><span style=\"font-weight: 400\">1560<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-11-20 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">3000<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-10-08 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">1500<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-10-08 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">4<\/span><\/td>\n<td><span style=\"font-weight: 400\">Monika<\/span><\/td>\n<td><span style=\"font-weight: 400\">2060<\/span><\/td>\n<td><span style=\"font-weight: 400\">2015-05-20 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">5<\/span><\/td>\n<td><span style=\"font-weight: 400\">Mike<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">6<\/span><\/td>\n<td><span style=\"font-weight: 400\">Phoebe<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">7<\/span><\/td>\n<td><span style=\"font-weight: 400\">Joey<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><span style=\"font-weight: 400\">c. Right Outer Join<\/span><\/h3>\n<p><span style=\"font-weight: 400\">Basically, even if there are no matches in the left table, HiveQL Right Outer Join returns all the rows from the right table. <\/span><\/p>\n<p><span style=\"font-weight: 400\">To be more specific, even if the ON clause matches 0 (zero) records in the left table, then also this Hive JOIN still returns a row in the result. Although, it returns with NULL in each column from the left table<\/span><\/p>\n<p><span style=\"font-weight: 400\">In addition, it returns all the values from the right table. Also, the matched values from the left table or NULL in case of no matching join predicate.<\/span><\/p>\n<p><span style=\"font-weight: 400\">However, the below query shows RIGHT OUTER JOIN between the CUSTOMER as well as ORDER tables.<\/span><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">notranslate\"&gt; hive&gt; SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c RIGHT OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);<\/pre>\n<p><span style=\"font-weight: 400\">Moreover, we get to see the following response, on the successful execution of the HiveQL Select query:<\/span><br \/>\n<strong>Table.5 &#8211; Right Outer Join in Hive<\/strong><\/p>\n<table>\n<tbody>\n<tr>\n<td><strong>ID<\/strong><\/td>\n<td><strong>Name<\/strong><\/td>\n<td><strong>Amount <\/strong><\/td>\n<td><strong>Date<\/strong><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">1300<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-10-08 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">1500<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-10-08 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">2<\/span><\/td>\n<td><span style=\"font-weight: 400\">Rachel<\/span><\/td>\n<td><span style=\"font-weight: 400\">1560<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-11-20 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">4<\/span><\/td>\n<td><span style=\"font-weight: 400\">Monika<\/span><\/td>\n<td><span style=\"font-weight: 400\">2060<\/span><\/td>\n<td><span style=\"font-weight: 400\">2015-05-20 00:00:00<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><span style=\"font-weight: 400\">d. Full Outer Join<\/span><\/h3>\n<p><span style=\"font-weight: 400\">The major purpose of this HiveQL Full outer Join is it combines the records of both the left and the right outer tables which fulfills the Hive JOIN condition. Moreover, this joined table contains either all the records from both the tables or fills in NULL values for missing matches on either side.<\/span><\/p>\n<p><span style=\"font-weight: 400\">However, the below query shows \u00a0FULL OUTER JOIN between CUSTOMER as well as ORDER tables:<\/span><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">hive&gt; SELECT c.ID, c.NAME, o.AMOUNT, o.DATE\nFROM CUSTOMERS c\nFULL OUTER JOIN ORDERS o\nON (c.ID = o.CUSTOMER_ID);<\/pre>\n<p><span style=\"font-weight: 400\">Moreover, we get to see the following response, on the successful execution of the query:<\/span><\/p>\n<p><strong>Table.6 &#8211; Full Outer Join in Hive<\/strong><\/p>\n<table>\n<tbody>\n<tr>\n<td><strong>ID<\/strong><\/td>\n<td><strong>Name<\/strong><\/td>\n<td><strong>Amount <\/strong><\/td>\n<td><strong>Date<\/strong><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">1<\/span><\/td>\n<td><span style=\"font-weight: 400\">Ross<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">2<\/span><\/td>\n<td><span style=\"font-weight: 400\">Rachel<\/span><\/td>\n<td><span style=\"font-weight: 400\">1560<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-11-20 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">3000<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-10-08 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">1500<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-10-08 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">4<\/span><\/td>\n<td><span style=\"font-weight: 400\">Monika<\/span><\/td>\n<td><span style=\"font-weight: 400\">2060<\/span><\/td>\n<td><span style=\"font-weight: 400\">2015-05-20 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">5<\/span><\/td>\n<td><span style=\"font-weight: 400\">Mike<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">6<\/span><\/td>\n<td><span style=\"font-weight: 400\">Phoebe<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">7<\/span><\/td>\n<td><span style=\"font-weight: 400\">Joey<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<td><span style=\"font-weight: 400\">NULL<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">3000 <\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-10-08 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">3<\/span><\/td>\n<td><span style=\"font-weight: 400\">Chandler<\/span><\/td>\n<td><span style=\"font-weight: 400\">1500<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-10-08 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">2<\/span><\/td>\n<td><span style=\"font-weight: 400\">Rachel<\/span><\/td>\n<td><span style=\"font-weight: 400\">1560<\/span><\/td>\n<td><span style=\"font-weight: 400\">2016-11-20 00:00:00<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">4<\/span><\/td>\n<td><span style=\"font-weight: 400\">Monika<\/span><\/td>\n<td><span style=\"font-weight: 400\">2060<\/span><\/td>\n<td><span style=\"font-weight: 400\">2015-05-20 00:00:00 <\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"font-weight: 400\"><span style=\"font-weight: 400\">This was all\u00a0about HiveQL Select &#8211; Apache Hive Join Tutorial. Hope you like our explanation of Types of Joins in Hive.<\/span><\/p>\n<h2><span style=\"font-weight: 400\">Conclusion<\/span><\/h2>\n<p><span style=\"font-weight: 400\">As a result, we have seen what is Apache Hive Join and possible types of Join in Hive- HiveQL Select. Also, we have seen several examples to understand Joins in Hive well. Moreover, we hope this article &#8220;Hive Join&#8221; will help a lot. Still, if any doubt occurs feel free to ask in the comment section.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Apache Hive, for combining specific fields from two tables by using values common to each one we use Hive Join &#8211; HiveQL Select Joins Query. However, we need to know the syntax of&#46;&#46;&#46;<\/p>\n","protected":false},"author":7,"featured_media":10623,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[4965,5711,5723,5742,5744,5775,5816,6726,7813,8200,11608],"class_list":["post-10426","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hive","tag-full-outer-join-example","tag-hive-full-outer-join","tag-hive-inner-join","tag-hive-join","tag-hive-left-outer-join","tag-hive-right-outer-join","tag-hiveql-select-joins","tag-inner-join-example","tag-joins-in-hive","tag-left-outer-join-example","tag-right-outer-join-example"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Hive Join | HiveQL Select Joins Query | Types of Join in Hive - DataFlair<\/title>\n<meta name=\"description\" content=\"Hive Join-HiveQL Select Joins Query, types of joins in hive, Inner join, Left &amp; Right Outer Join, Full Outer Joins in Hive with examples, hive join clause\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/hive-join\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Hive Join | HiveQL Select Joins Query | Types of Join in Hive - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Hive Join-HiveQL Select Joins Query, types of joins in hive, Inner join, Left &amp; Right Outer Join, Full Outer Joins in Hive with examples, hive join clause\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/hive-join\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-09T11:52:01+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-joins-01.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Hive Join | HiveQL Select Joins Query | Types of Join in Hive - DataFlair","description":"Hive Join-HiveQL Select Joins Query, types of joins in hive, Inner join, Left & Right Outer Join, Full Outer Joins in Hive with examples, hive join clause","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/hive-join\/","og_locale":"en_US","og_type":"article","og_title":"Hive Join | HiveQL Select Joins Query | Types of Join in Hive - DataFlair","og_description":"Hive Join-HiveQL Select Joins Query, types of joins in hive, Inner join, Left & Right Outer Join, Full Outer Joins in Hive with examples, hive join clause","og_url":"https:\/\/data-flair.training\/blogs\/hive-join\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-09T11:52:01+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-joins-01.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/hive-join\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/hive-join\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd"},"headline":"Hive Join | HiveQL Select Joins Query | Types of Join in Hive","datePublished":"2018-03-09T11:52:01+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-join\/"},"wordCount":1087,"commentCount":5,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-join\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-joins-01.jpg","keywords":["full outer join example","hive full outer join","Hive inner join","Hive Join","hive left outer join","hive right outer join","HiveQL Select Joins","inner join example","joins in hive","left outer join example","right outer join example"],"articleSection":["Hive Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/hive-join\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/hive-join\/","url":"https:\/\/data-flair.training\/blogs\/hive-join\/","name":"Hive Join | HiveQL Select Joins Query | Types of Join in Hive - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-join\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-join\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-joins-01.jpg","datePublished":"2018-03-09T11:52:01+00:00","description":"Hive Join-HiveQL Select Joins Query, types of joins in hive, Inner join, Left & Right Outer Join, Full Outer Joins in Hive with examples, hive join clause","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/hive-join\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/hive-join\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/hive-join\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-joins-01.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-joins-01.jpg","width":1200,"height":628,"caption":"Hive Join - HiveQL Select Joins Query"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/hive-join\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Hive Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/hive\/"},{"@type":"ListItem","position":3,"name":"Hive Join | HiveQL Select Joins Query | Types of Join in Hive"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"DataFlair Team specializes in creating clear, actionable content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Backed by industry expertise, we make learning easy and career-oriented for beginners and pros alike.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam3\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10426","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=10426"}],"version-history":[{"count":0,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10426\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/10623"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=10426"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=10426"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=10426"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}