title: Correlated Subquery in Spark SQL
body: <p>I have the following 2 tables for which I have to check the existence of values between them using a <strong>correlated</strong> sub-query.</p>
  <p>The requirement is - for each record in the <code>orders</code> table check if the corresponding <code>custid</code> is present in the <code>customer</code> table, and then output a field (named <code>FLAG</code>) with value <code>Y</code> if the <code>custid</code> exists, otherwise <code>N</code> if it doesn't.</p>
  <p>orders:</p>
  <pre><code>orderid | custid
  12345   | XYZ
  34566   | XYZ
  68790   | MNP
  59876   | QRS
  15620   | UVW
  </code></pre>
  <p>customer:</p>
  <pre><code>id | custid
  1  | XYZ
  2  | UVW
  </code></pre>
  <p>Expected Output:</p>
  <pre><code>orderid | custid  | FLAG
  12345   | XYZ     | Y
  34566   | XYZ     | Y 
  68790   | MNP     | N
  59876   | QRS     | N
  15620   | UVW     | Y
  </code></pre>
  <p>I tried something like the following but couldn't get it to work -</p>
  <pre><code>select 
  o.orderid,
  o.custid,
  case when o.custid EXISTS (select 1 from customer c on c.custid = o.custid)
       then 'Y'
       else 'N'
  end as flag
  from orders o
  </code></pre>
  <p>Can this be solved with a <strong>correlated scalar</strong> sub-query ? If not what is the best way to implement this requirement ?</p>
  <p>Please advise.</p>
  <p><em>Note: using Spark SQL query v2.4.0</em></p>
  <p>Thanks.</p>