title: Order of table joining in SparkSQL for better performance
body: <p>I am new to <code>Spark-SQL</code> to read <code>Hive</code> tables. I want to know that how does Spark performs a <strong>multi-table</strong> <code>Join</code>. I read somewhere that it is recommended to always keep the <strong>largest</strong> table on the top of the Join order and so on, which is conducive for <code>Join</code> efficiency. I read that in a Join, Spark loads the first table (largest) in the order to Memory and streams the other table which aids in Join performance. However, I am confused as to how this strategy will boost performance since the largest table (in most cases) will not fit in memory and spill on disk.</p>
  <p>Can anyone please clarify and explain the Joining mechanism employed by Spark under the hood when joining
  [<code>large</code> vs <code>medium</code>], [<code>large</code> vs <code>small</code>] and [<code>large</code> vs <code>large</code>] tables in terms of Join types (<code>inner</code> &amp; <code>outer</code>) and Join performance. I want to know the best practices that should be followed in terms of Join table ordering to achieve optimal performance for all the join strategies (SMJ, ShuffleHash &amp; Broadcast) employed by Spark. Let's assume the following query:</p>
  <pre><code>select 
  a.id, 
  b.cust_nm, 
  c.prod_nm
  from large_tab a
  join medium_tab b
  on a.id = b.id
  join small_tab c
  on a.pid = c.pid;
  </code></pre>
  <p>Note: We use Spark 2.4</p>
  <p>Any help is much appreciated. Thanks.</p>