OUTER APPLY in BigQuery

Question

I want to apply a column to a list of users where I pull the latest sign in date . Traditionally I would do something like this:

SELECT U.* , O.* FROM Users.Users U 
OUTER APPLY ( SELECT .. FROM .. Events.Events E WHERE E.UserId = U.UserId) O

However BigQuery doesn't seem to recognize the Outer Apply keywords. What Am I doing wrong, is there a substitute for above?

Answer 1

This approach seems to work:

SELECT U.*, O.*
FROM Users.Users U
LEFT JOIN UNNEST((
  SELECT array_agg(STRUCT(...))
  FROM Events.Events E
  WHERE E.UserId = U.UserId
)) ON TRUE

Of course, in your simplified case, you don't actually need OUTER APPLY (or standard SQL LEFT JOIN LATERAL). You'd just use an ordinary LEFT JOIN. I'm assuming your outer applied derived table does something more fancy that requires this operator.