Subquery vs join

SQL++
1

A little confused about when to use which for a simple join by key.

given a document like this:
document a
{
“b”: “key”
}

What is the difference between the 2 queries, and which is preferred?

select a, (select b from b use keys a.b)[0] from a use keys 'key'

select a, b from a use keys 'key' join b on keys a.b

Thanks!

2

First query become correlated subquery. So each outer row subquery is evaluated. If a.b is array the subquery results can be array by doing [0] you are only selecting first element.

a.b is array of n elements join query produces n documents i.e. a joins with each b . You can also have WHERE clause with predicate between a and b.