SQL Join equivalent

How to use flow script to perform the equivalent of a SQL join between two table variables (not to be confused with the flow script join command, which does something else)

In the code below two dummy tables are created, usr and org. The LeftJoin variable ("let LeftJoin"...) does the join between the tables with the FirstOrEmpty function. The result will be equal to a left join in SQL.

let usr = [name: "bob", id: 45, orgid: 1001]
& [name: "bill", id: 46, orgid: 1002]
& [name: "joe", id: 47, orgid: 1002]
& [name: "Anna", id: 47, orgid: 1004]; // this org does not exist 
 
let org = [orgname: "Ikea", id: 1001]
& [orgname: "Novacura", id: 1002]
& [orgname: "Flowington", id: 1003]; //This org has no users
  
let LeftJoin = select name, id, firstOrEmpty(org where id = orgid).orgname as orgname, orgid from usr;

let usersWithOrg = usr where any(org where id = orgid);
let usersWithoutOrg = usr where not any(org where id = orgid);


return LeftJoin;

The usersWithOrg and usersWithoutOrg variables are similar to the "where in..." functionality in SQL, resulting in either the rows in the usr-table that does or does not have a corresponding row in the org table.

By returning the different variables "LeftJoin", "usersWithOrg" or "usersWithoutOrg" in the last row in the code you can view the different results (but note as the left join yields another column from the joined table the numbers of columns in the output will be different)