title: SQL array flattening: Why doesn't CROSS JOIN UNNEST join every nested value with every row?
body: <p>This question isn't about solving a particular problem, it's about understanding what's actually happening behind the scenes in a common SQL idiom used to flatten arrays.  There's some magic behind the scenes and I want to peek behind the curtain of syntactic sugar and see what's going on.</p>
  
  <p>Let's consider the following table <code>t1</code> :</p>
  
  <p><a href="https://i.stack.imgur.com/mAouG.png" rel="noreferrer"><img src="https://i.stack.imgur.com/mAouG.png" alt="t1"></a></p>
  
  <p>Now let's assume we have a function called <code>FLATTEN</code> that takes a column of type array and unpacks each of the arrays in that column so that we're left with one row for each value in each array -- if we run <code>SELECT FLATTEN(numbers_array) AS flattened_numbers FROM t1</code>, we'd expect the following, which we'll call <code>t2</code></p>
  
  <p><a href="https://i.stack.imgur.com/O59l3.png" rel="noreferrer"><img src="https://i.stack.imgur.com/O59l3.png" alt="t2"></a></p>
  
  <p>In SQL, the CROSS JOIN combines rows from two tables by combining each row from the first table with each row from the second table. So if we run <code>SELECT id, flattened.flattened_numbers from t1 CROSS JOIN flattened</code>, we get</p>
  
  <p><a href="https://i.stack.imgur.com/82KBt.png" rel="noreferrer"><img src="https://i.stack.imgur.com/82KBt.png" alt="enter image description here"></a></p>
  
  <p>Now flatten is just an imaginary function, and as you can see it's not very useful to combine it with a CROSS JOIN, because each of the original values of the <code>id</code> column gets mixed with <code>flattened_numbers</code> from each of the original rows.  Everything gets mixed up because we don't have a <code>WHERE</code> clause that selects only the rows of the <code>CROSS JOIN</code> that we want.</p>
  
  <p>The pattern people actually use to flatten arrays looks like this:
  <code>SELECT id, flattened_numbers FROM t1 CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers</code>, which produces</p>
  
  <p><a href="https://i.stack.imgur.com/ZFkaf.png" rel="noreferrer"><img src="https://i.stack.imgur.com/ZFkaf.png" alt="enter image description here"></a></p>
  
  <p>But I don't understand why the <code>CROSS JOIN UNNEST</code> pattern actually works.  Because the <code>CROSS JOIN</code> doesn't include a <code>WHERE</code> clause, I would expect it behave just like the <code>FLATTEN</code> function I outlined above, where every unnested value gets combined with every row from <code>t1</code>.</p>
  
  <p>Can someone 'unpack' what's actually going on in the <code>CROSS JOIN UNNEST</code> pattern that ensures that each row is only joined with it's own nested values (and not with the nested values from other rows)?</p>