title: BigQuery: LEFT JOIN without field equality
body: <p>Here is what I have:</p>
  
  <pre><code>| customId | role     | teamLeadIds |
  | sl1      | salesRep | [tl1]       |
  | sl2      | salesRep | [tl1]       |
  | sl3      | salesRep | [tl2]       |
  | tl1      | teamLead | [tl3]       |
  | tl2      | teamLead | [tl3]       |
  | tl3      | teamLead | []          |
  </code></pre>
  
  <p>Here is what i need:</p>
  
  <pre><code>| customId | teamLeadIds |
  | sl1      | [tl1, tl3]  |
  | sl2      | [tl1, tl3]  |
  | sl3      | [tl2, tl3]  |
  </code></pre>
  
  <p>Here is what I do:</p>
  
  <pre><code>SELECT 
    users.customId,
    ARRAY(SELECT DISTINCT teamLeadIds 
      FROM UNNEST(ARRAY_CONCAT(users.teamLeadIds, teamleads_1.teamLeadIds, teamleads_2.teamLeadIds)) teamLeadIds 
      ORDER BY teamLeadIds
    ) AS teamLeadIds
  FROM ( SELECT * FROM users WHERE role = 'salesRep') users
  JOIN ( SELECT * FROM users WHERE role = 'teamLead' ) teamleads_1 ON teamleads_1.customId IN UNNEST(users.teamLeadIds)
  JOIN ( SELECT * FROM users WHERE role = 'teamLead' ) teamleads_2 ON teamleads_2.customId IN UNNEST(teamleads_1.teamLeadIds)
  </code></pre>
  
  <p>This works on this dataset:</p>
  
  <pre><code>WITH users AS (
    SELECT 'sl1' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
    SELECT 'sl2' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
    SELECT 'sl3' AS customId, 'salesRep' AS role, ['tl2'] AS teamLeadIds UNION ALL
    SELECT 'tl1' AS customId, 'teamLead' AS role, ['tl3'] AS teamLeadIds UNION ALL
    SELECT 'tl2' AS customId, 'teamLead' AS role, ['tl3'] AS teamLeadIds UNION ALL
    SELECT 'tl3' AS customId, 'teamLead' AS role, [] AS teamLeadIds
  )
  </code></pre>
  
  <p>But doesn't work on this dataset (no 2nd level of nesting):</p>
  
  <pre><code>WITH users AS (
    SELECT 'sl1' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
    SELECT 'sl2' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
    SELECT 'sl3' AS customId, 'salesRep' AS role, ['tl2'] AS teamLeadIds UNION ALL
    SELECT 'tl1' AS customId, 'teamLead' AS role, [] AS teamLeadIds UNION ALL
    SELECT 'tl2' AS customId, 'teamLead' AS role, [] AS teamLeadIds
  )
  </code></pre>
  
  <p>Query returns no results in the latter case. Apparently, I need <code>LEFT JOIN</code>, but it's not allowed: </p>
  
  <pre><code>LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
  </code></pre>