Most Optimum Way to JOIN Documents

Hello @vsri
Here is the new thread:
We are trying to find the most optimum way to JOIN several documents:
Given:

Document 1:
Bucket users
inst::XYZ
{
“uid”: 11,
“name”: "ABC,
“tp”: “inst”,
}
Bucket places
Document 2:
u::1
{
“id”:1,
“lstname”:“vaca”
“tp”:“u”
}

Indexes:
CREATE INDEX idx_baker1 ON users(uid,name) WHERE (tp = ‘inst’) USING GSI;
CREATE INDEX idx_baker2 ON places (lstname,id) WHERE (tp = ‘u’) USING GSI;

Original Queries:

Query 1:
select inst.name, inst.uid, u.id
from users inst join places u on keys [‘u:’||TO_STRING(inst.uid)]
where inst.tp = ‘inst’ and u.tp = ‘u’ and inst.uid = ‘abc’ and u.lstname = ‘vaca’

Query 2:
select inst.name, inst.uid, u.id
from users inst join places u on keys (select raw meta(u2).id from users u2 where u2.tp = ‘u’ and u.lstname = ‘vaca’ )
where u.id = inst.uid and inst.tp = ‘inst’ and u.tp = ‘u’ and inst.uid = ‘abc’

Couchbase suggested Queries:

Alternative A:

“The following avoids JOINs and uses covered indexes. Check how it performs.”

SELECT inst.name, inst.uid, pu.id FROM users inst
LET pu = (SELECT u.id, META(u).id AS mid FROM places u WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’)[0]
WHERE inst.tp = ‘inst’ AND inst.uid = ‘abc’ AND pu.mid = u:’||TO_STRING(inst.uid);

SELECT inst.name, inst.uid, u2.id
LET pu = (SELECT u.id, META(u).id AS mid FROM places u WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’)
FROM users inst UNNEST pu AS u2
WHERE inst.tp = ‘inst’ AND inst.uid <> ‘abc’ AND u2.id = inst.uid;

Alternative B:

CREATE INDEX idx_baker3 ON users(“u::”||uid,uid,name) WHERE (tp = ‘inst’) USING GSI;
SELECT inst.name, inst.uid, u.id
FROM places u
JOIN users inst ON KEY “u::”||inst.uid FOR places
WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’ AND inst.tp = ‘inst’ AND inst.uid = ‘abc’;

SELECT inst.name, inst.uid, u.id
FROM places u
JOIN users inst ON KEY “u::”||inst.uid FOR places
WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’ AND inst.tp = ‘inst’ AND inst.uid = ‘abc’ AND u.id = inst.uid;

Originally queries and Alternative queries seem to have a very similar plans.
We moved both sets of documents to the same buckets, but were not able to get a covered index.
We tried creating the following index on all predicates of Alternative A
CREATE INDEX idx_type_baker3 ON users (tp,uid,name,id,lstname) WHERE (tp= ‘inst’ or tp = ‘u’) USING GSI;
A) does this index make sense? B) what would happen if the object e.g “name” existed in both documents? C) is it a good practice to have WHERE (tp= ‘inst’ or tp = ‘u’ or … ) in the indexx

Alternative B
The second query does not match the scope of the second original query. Additionally, we were not able to join more than two documents using the ON …KEY …FOR, which is a bit of a limitation.
D) Can you multiple JOINs with this syntax?

E) Please help us understand which one of the 3 sets of queries is most efficient (speed and cost)?
F) In this context, does it make sense to merge all buckets into one bucket to make everybody’s life easier :slight_smile:

Many thanks for your assistance in advanced,

BG

  1. What is Couchbase version you are using?
    A)This Index does not make sense. WHERE clauses only tells what document index. It will not merge the two documents.
    B) This leads two index entries and you need to Join them. Unless you embedded the documents based on relation.
    C) Also Partial index with OR clauses not recommended and index may not choose.

Alternative A : Should have used covered index. If not used post the EXPLAIN

What is problem with Alternative B Second Query. Can you post couple of sample documents and expected results.

E) It depends on your data etc. You can run all there queries and see which gives better speed .
F) Unless you embedded the documents merging the buckets will not help.

Second Query 2 in this post and other post is different. example inst.uid = ‘abc’ vs inst.uid <> ‘abc’

Also Sample documents uid , name doesn’t match with queries.
Example: Predicate inst.uid = ‘abc’ but you have “uid”:11
Also [‘u:’||TO_STRING(inst.uid)] there is no matching document.
Please provide right values so that I can verify before posting any suggestions.

we are using 4.6.2

Here is what I did:
1- Create bucket test-one with the following documents:

u:1 {“id”:1,“lstname”:“vaca”,“tp”:“u”}
u:2 {“id”:2,“lstname”:“vaca”,“tp”:“u”}
u:3 {“id”:3,“lstname”:“john”,“tp”:“u”}

2- Create bucket test-two with the following documents:
inst:asdfg {“uid”:2,“name”:“DEF”,“tp”:“inst”}
inst:qwerty {“uid”:1,“name”:“ABC”,“tp”:“inst”}
inst:tyuio {“uid”:4,“name”:“IJK”,“tp”:“inst”}
inst:zxcvb {“uid”:3,“name”:“GHI”,“tp”:“inst”}

3- create the following indexes:
CREATE PRIMARY INDEX test-one ON test-one
CREATE PRIMARY INDEX test-two ON test-two
CREATE INDEX idx_baker2 ON test-one(lstname,id) WHERE (tp = ‘u’) USING GSI;
CREATE INDEX idx_baker1 ON test-two(“u:”||uid,uid,name) WHERE (tp = ‘inst’) USING GSI;
CREATE INDEX idx_baker3 ON test-two(“u:”||uid) WHERE (tp = ‘inst’) USING GSI;
CREATE INDEX idx_baker4 ON test-two(uid,name) WHERE (tp = ‘inst’) USING GSI;

Step 1: run the Original Queries:

select inst.name, inst.uid, u.id
from test-two inst join test-one u on keys [‘u:’||TO_STRING(inst.uid)]
where inst.tp = ‘inst’ and u.tp = 'u’and inst.uid <> “” and u.lstname = ‘vaca’

NOTE: We added inst.uid <> “” so idx_baker4 can be used as cover otherwise the primary index is used

Queries has a cover on the left doc test-two inst using idx_baker4

select inst.name, inst.uid, u.id
from test-two inst join test-one u on keys (select raw meta(u2).id from test-one u2 where u2.tp = ‘u’ and u2.lstname = ‘vaca’ )
where u.id = inst.uid and inst.tp = ‘inst’

Queries had a cover on the left doc test-two inst using idx_baker4

RESULTS:
[
{
“id”: 2,
“name”: “DEF”,
“uid”: 2
},
{
“id”: 1,
“name”: “ABC”,
“uid”: 1
}
]

Step 2: run the Alternative A Queries:

SELECT inst.name, inst.uid, pu.id FROM test-two inst
LET pu = (SELECT u.id, META(u).id AS mid FROM test-one u WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’)[0]
WHERE inst.tp = ‘inst’ AND pu.mid = ‘u:’||TO_STRING(inst.uid);

Queries had a cover on the left doc test-two inst using idx_baker4
RESULTS:
[
{
“id”: 1,
“name”: “ABC”,
“uid”: 1
}
]

Expected results

[
{
“id”: 2,
“name”: “DEF”,
“uid”: 2
},
{
“id”: 1,
“name”: “ABC”,
“uid”: 1
}
]

PROBLEM: because “pu” is an array you need to know the array index of interest before hand, in this case [0] was specified. However, normally we would not know the index.

SELECT inst.name, inst.uid, u2.id
LET pu = (SELECT u.id, META(u).id AS mid FROM test-one u WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’)
FROM test-two inst UNNEST pu AS u2
WHERE inst.tp = ‘inst’ AND u2.id = inst.uid;

Query did not execute
[
{
“code”: 3000,
“msg”: “syntax error - at FROM”,
“query_from_user”: “SELECT inst.name, inst.uid, u2.id\n LET pu = (SELECT u.id, META(u).id AS mid FROM test-one u WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’)\n FROM test-two inst UNNEST pu AS u2\n WHERE inst.tp = ‘inst’ AND u2.id = inst.uid;”
}
]

Step 3: run the Alternative B Queries:

SELECT inst.name, inst.uid, u.id
FROM test-one u JOIN test-two inst ON KEY (“u:”||inst.uid) FOR u
WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’ AND inst.tp = ‘inst’ ;

{
“results”: [],
“metrics”: {
“elapsedTime”: “33.204286ms”,
“executionTime”: “33.143927ms”,
“resultCount”: 0,
“resultSize”: 0
}
}
Expected Results
[
{
“id”: 2,
“name”: “DEF”,
“uid”: 2
},
{
“id”: 1,
“name”: “ABC”,
“uid”: 1
}
]

It does not seem to get the expected results.

SELECT inst.name, inst.uid, u.id
FROM places u
JOIN users inst ON KEY “u:”||inst.uid FOR u
WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’ AND inst.tp = ‘inst’ AND inst.uid <> ‘abc’ AND u.id = inst.uid;

This query assumes that the joining key (“u:”||inst.uid) is deterministic. However, in our case we need to have a subquery to find the keys.

Questions:
1- Original and Alternative A appear to have almost an identical plan, which one of two has a better performance / recommended by CB?
2- Both,Original queries and Alternative A, have a cover on the left only in the explain. What is happening in the “#operator”: “Parallel” and “#operator”: “Let” or “#operator”: “join” ? is CB fetching all the documents of the right side then applying the predicates?
3- Please help us sort out the syntax issue with the second query of Alternative A. This could be useful for our use case.
4- The JOIN ON KEY … FOR does not appear to fetch the expected results. It is getting 0 hits. Please help us understand why?
5- The JOIN ON KEY … FOR joins only two docs? or you can join multiple documents? if you can join more than two please provide syntax.

The above details are just an example. Our actual queries has 6 joins on 4 buckets. I would like to send you one the queries in private so you can take a look at it and may be help us bring the execution time of the index from seconds to milliseconds. Please let me know how we can do this?

Many Thanks in advanced for you assistance,

BG

INSERT INTO b1 VALUES ("u:1",{"id":1,"lstname":"vaca","tp":"u"}),
               VALUES ("u:2",{"id":2,"lstname":"vaca","tp":"u"}),
               VALUES ("u:3",{"id":3,"lstname":"john","tp":"u"});

INSERT INTO b2 VALUES ("inst:asdfg",{"uid":2,"name":"DEF","tp":"inst"}),
               VALUES ("inst:qwerty",{"uid":1,"name":"ABC","tp":"inst"}),
               VALUES ("inst:tyuio",{"uid":4,"name":"IJK","tp":"inst"}),
               VALUES ("inst:zxcvb",{"uid":3,"name":"GHI","tp":"inst"});


CREATE INDEX ix1_b1 ON b1 (lstname,id) WHERE (tp = 'u') USING GSI;
CREATE INDEX ix1_b2 ON b2 (uid, name) WHERE (tp = 'inst') USING GSI;
CREATE INDEX ix2_b2 ON b2 ("u:"||TOSTRING(uid),uid,name) WHERE (tp = 'inst') USING GSI;

Q1-O1)

SELECT inst.name, inst.uid, u.id
FROM b2 inst JOIN b1 u ON KEYS 'u:'||TOSTRING(inst.uid)
WHERE inst.tp = 'inst' AND u.tp = 'u' AND inst.uid IS NOT NULL AND u.lstname = 'vaca';

Above query is LOOK UP JOIN and uses ix1_b2 As covered on LEFT side and Fetched b1. If the selectivity is high on LEFT side of JOIN this will performs well.

Q1-A1)

SELECT inst.name, inst.uid, inst.uid AS id
       FROM b2 inst
       WHERE inst.tp = 'inst' AND inst.uid IN (SELECT RAW u.id FROM b1 u WHERE u.tp = 'u' AND u.lstname = 'vaca');

Above query uses ix1_b2 as covered and SUB query use ix1_b1 as covered and non correlated, i.e evaluated once. So whole query makes covered.

Q1-A2)

SELECT inst.name, inst.uid, u.id
    FROM b1 u
    JOIN b2 inst ON KEY "u:"||TOSTRING(inst.uid) FOR u
    WHERE u.tp = 'u' AND u.lstname = 'vaca' AND inst.tp = 'inst' AND inst.uid IS NOT NULL;

Explanation of Q2 follows same Q1

Q2-O1)

SELECT inst.name, inst.uid, u.id
    FROM b2 inst JOIN b1 u ON KEYS (SELECT RAW META(u2).id FROM b1 u2 WHERE u2.tp = 'u' AND u2.lstname = 'vaca' )
WHERE u.id = inst.uid AND inst.tp = 'inst';

Q2-A1)

SELECT inst.name, inst.uid, u_id AS id FROM (
             SELECT inst.name, inst.uid, pu
                    FROM b2 inst
                    LET pu = (SELECT RAW u.id FROM b1 u WHERE u.tp = 'u' AND u.lstname = 'vaca')
                    WHERE inst.tp = 'inst' AND inst.uid IS NOT NULL) AS inst UNNEST inst.pu AS u_id
      WHERE inst.uid = u_id;

Q2-A2)

SELECT inst.name, inst.uid, u.id
    FROM b1 u
    JOIN b2 inst ON KEY "u:"||TOSTRING(inst.uid) FOR u
    WHERE u.tp = 'u' AND u.lstname = 'vaca' AND inst.tp = 'inst' AND inst.uid = u.id;

Above query uses ix1_b1, ix2_b2 as Covered Index JOIN. This performs better when you have high selectivity on b1 and have relation ship.

  1. Explained above. It all depends on data and selectivity. Try all options with actual data choose one.
  2. LOOKP JOINs only covered LEFT side and RIGHT side does fetch. Index Join can be covered both sides. If not covered it does Fetch
  3. New queries will work without any errors
  4. Check new query it does
  5. It will please. It takes META(u).id (i.e FOR u) and does Index Scan using META(u)id on ix2_b2( i.e ON KEY “u:”||TOSTRING(inst.uid)). If the Index Scan result multiple items it joins all of them with left item.
    checkout https://dzone.com/articles/join-faster-with-couchbase-index-joins
    https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/from.html
    https://dzone.com/articles/visually-explaining-n1ql-joins

Please checkout the links and see if you can improve, If still have issues post them here.

In our example they are because we have a one-to-one relationship between
the two docs. In case you have one-to-many relationship the second query is
unavoidable. In other words, inst.uid is not present in the inst doc.

Updated previous post for Q2 also.
FYI: inst.uid used any where in predicate means it is NOT MISSING and NOT NULL.

vsr1,

Thanks for the prompt response;

Here is what I found:
Q1-A1)

The JOIN was simplified to a subquery by replacing u.id with inst.uid in the SELECT. However, in our use case, the SELECT has many more attributes that belong to u. Such as u.lstname.

1- What would the query look like using the LET function?
2- Using LET instead of an a JOIN seems to have the same plan. That is to say, cover for the b2 inst and fetch for the LET. Please confirm?

Q2-A1)
SELECT inst.name, inst.uid, u_id AS id FROM (
SELECT inst.name, inst.uid, pu
FROM b2 inst
LET pu = (SELECT RAW u.id FROM b1 u WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’)
WHERE inst.tp = ‘inst’ AND inst.uid IS NOT NULL) AS inst UNNEST inst.pu AS u_id
WHERE inst.uid = u_id;
3- Do we still have a cover for b2 inst and a fetch for the LET?

Q2-A2)
It does not work because we cannot construct “u:”||TOSTRING(inst.uid) as shown. We need to have a sub-query to get the keys.
4- Is there a way to replace “u:”||TOSTRING(inst.uid) with a query?

5- I did not quite understand your explanation of how to use the ON KEY …FOR with multiple JOINS. Assuming, in addition to b1 inst and b2 u, we have b3 x, How would the syntax of the ON KEY …FOR look like for the following query?

SELECT u.uid, u.lstname, x.city
FROM b1 inst JOIN b2 u ON KEYS SELECT RAW META(u2).id FROM b1 u2 WHERE u2.tp = ‘u’ AND u2.lstname = ‘vaca’ JOIN b3 x ON KEYS “u:”||TOSTRING(x.uid)
Where bla bla bla

Many Thanks

B

  1. I provided query based on your original query. If the original query changes Alternative query changes.
    Some thing like below

      SELECT inst.name, inst.uid, u.id, u.lstname FROM (
              SELECT inst.name, inst.uid, pu
                     FROM b2 inst
                     LET pu = (SELECT u.id , u.lstname FROM b1 u WHERE u.tp = 'u' AND u.lstname = 'vaca')
                     WHERE inst.tp = 'inst' AND inst.uid IS NOT NULL) AS inst UNNEST inst.pu AS u
       WHERE inst.uid = u.id;
    
  2. Right side of LOOKUP JOIN can’t be covered and required fetch and you have highly selective predicate on right side (i.e. u.lstname = ‘vaca’). So LET evaluates query using covered index and pu becomes ARRAY of values. Use them in rest of the query

  3. Both of them will be covered. If you want you can try this,
    EXPLAIN SELECT RAW u.id FROM b1 u WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’;
    EXPLAIN SELECT inst.name, inst.uid FROM b2 inst WHERE inst.tp = ‘inst’ AND inst.uid IS NOT NULL;

  4. No subquery. It will work please read through above links and understand how index Joins work.

Thanks @vsr1 for the detailed response.
Here is a summery of what I understood from our discussion:

1- The most efficient JOIN is using the format, ON KEY … FOR as it uses the INDEXJOIN and you can have COVER for the entire query using two separate indexes

SELECT inst.name, inst.uid, u.id
FROM b1 u
JOIN b2 inst ON KEY “u:”||TOSTRING(inst.uid) FOR u
WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’ AND inst.tp = ‘inst’ AND inst.uid = u.id;

Limitations:
1A) You can only JOIN two documents. The Syntax does not support 3 or more JOINs. Please Confirm?
1B) What comes after the FOR has to be indexed (You cannot have a query to fetch the keys), Please Confirm?

2- For JOINs where all or the majority of the WHERE predicates are on the LEFT side document. CB filters WHERE predicates of the LEFT document using a COVER index of the LEFT document (if it exists) while CB Fetches all RIGHT documents if there are any WHERE predicates related to them regardless of whether or not a COVER index for those predicates exists.

SELECT inst.name, inst.uid, u.id
FROM b2 inst
JOIN b1 u ON KEYS “u:”||TOSTRING(inst.uid)
WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’ AND inst.tp = ‘inst’ AND inst.uid IS NOT NULL;

Remarks
2A) Predicates on the RIGHT side JOINs will results in the fetch of a very large number of data.
2B ) There is no need to create indexes on the predicates of the RIGHT hand documents since CB always fetch the entire documents before start filtering. Please confirm?
2C) An index will need to be created on “u:”||TOSTRING(inst.uid). Please confirm?

3- For JOINs where all or the majority of the WHERE predicates are on the RIGHT side documents.

SELECT inst.name, inst.uid, u.id, u.lstname FROM (
SELECT inst.name, inst.uid, pu
FROM test-two inst
LET pu = (SELECT u.id , u.lstname FROM test-one u WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’)
WHERE inst.tp = ‘inst’ AND inst.uid IS NOT NULL) AS inst UNNEST inst.pu AS u
WHERE inst.uid = u.id;

Remarks:
3A) Multiple JOINs can be represented by multiple variables using the LET statement. Please confirm?
3B) All WHERE Predicates related to the specific documents will built in the SUB-QUERY. The SUB-QUERY will use a COVER if available.
3C) To evaluate the WHERE predicates of the outside SELECT, CB will use the predicates of the inside SELECT including the LET arrays. Hence, No document fetching will be performed. Please confirm? Are we fetching any documents in the BOLD sections of the explain?

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“covers”: [
“cover ((inst.uid))”,
“cover ((inst.name))”,
“cover ((meta(inst).id))”
],
“filter_covers”: {
“cover ((inst.tp))”: “inst”
},
“index”: “idx_baker4”,
“index_id”: “4030b775950f1dd2”,
“keyspace”: “test-two”,
“namespace”: “default”,
“spans”: [
{
“Exact”: true,
“Range”: {
“Inclusion”: 0,
“Low”: [
“null”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((cover ((inst.tp)) = “inst”) and (cover ((inst.uid)) is not null))”
},
{
"#operator": “Let”,
“bindings”: [
{
“expr”: “(select (u.id), (u.lstname) from test-one as u where (((u.tp) = “u”) and ((u.lstname) = “vaca”)))”,
“var”: “pu”
}
]
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “cover ((inst.name))”
},
{
“expr”: “cover ((inst.uid))”
},
{
“expr”: “pu
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Alias”,
“as”: “inst”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Unnest”,
“as”: “u”,
“expr”: “(inst.pu)”
}
]
}
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
"condition": “((inst.uid) = (u.id))”
},
{
"#operator": “InitialProject”,
"result_terms": [
{
“expr”: “(inst.name)”
},
{
“expr”: “(inst.uid)”
},
{
“expr”: “(u.id)”
},
{
“expr”: “(u.lstname)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT inst.name, inst.uid, u.id, u.lstname FROM (\n SELECT inst.name, inst.uid, pu\n FROM test-two inst\n LET pu = (SELECT u.id , u.lstname FROM test-one u WHERE u.tp = ‘u’ AND u.lstname = ‘vaca’)\n WHERE inst.tp = ‘inst’ AND inst.uid IS NOT NULL) AS inst UNNEST inst.pu AS u\n WHERE inst.uid = u.id;”
}
]

Thanks a million for all the assistance. I am sure this thread will be very helpful clarifying things to the community.

B

Hi @Baker,
Couchbase have variety of joins LOOK UP JOINs, INDEX JOINS, UNNEST, NEST
Query can have multiple joins , The JOINS are done LEFT to RIGHT.
Any predicates on first bucket of LEFT most JOIN are pushed to indexer if possible and gets the qualified results all the right side of predicates mostly applied post join
Each Join has its own advantages there is no efficient one. It all depends on what relation you have which side has high selectivity.
INDEX JOIN can have more than one JOINs (earlier i said one, i am wrong). It needs to follow the Index Join rules (i.e Need to have index, the leading index key needs to be expression after ON KEY, After the FOR needs to be bucket and needs to be one of the LEFT side of JOIN)
Right side of LOOK UP JOIN always FETCH and no need index.
"#operator": “Fetch”, “#operator”: “Join” only Fetches documents all other operators are various phases of execution tree.
LET is another way declaring variable and storing expression evaluation, use in the query.

Thank you @vsr1 I very much appreciate the response.
Could you give me an explicit query example using INDEX JOIN joining 3 or more documents. I could not find any reference to that effect. All resources seem to always show a JOIN of 2 tables
Thanks again
B

CREATE INDEX ix1 ON default (k0, k1,k2) ;
CREATE INDEX ix2 ON default (onk1, k10,k12) ;
CREATE INDEX ix3 ON default (onk2, k20,k21) ;

– 2 Index Joins both using b1, b2, one lookup join
SELECT b1.k1, b2.k10, b3.k20
FROM default b1
JOIN default b2 ON KEY b2.onk1 FOR b1
JOIN default b3 ON KEY b3.onk2 FOR b2
JOIN default b4 ON KEYS b3.k21
WHERE b1.k0 > 10;

1 Like