{"id":8085,"date":"2020-01-21T10:30:47","date_gmt":"2020-01-21T18:30:47","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=8085"},"modified":"2024-09-12T00:46:45","modified_gmt":"2024-09-12T07:46:45","slug":"ansi-join-enhancements-and-ansi-merge","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/","title":{"rendered":"ANSI JOIN Enhancements and ANSI MERGE"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

N1QL support for ANSI JOIN was first introduced in version 5.5. Currently there is a restriction in ANSI JOIN (version 5.5 and 6.0) in that the right-hand side of an ANSI JOIN must be a keyspace. This restriction will be lifted in version 6.5.<\/span><\/p>\n

N1QL support for MERGE statement currently uses ON KEYS clause, similar to lookup join. ANSI MERGE syntax will be supported in version 6.5.<\/span><\/p>\n

 <\/p>\n

ANSI JOIN Enhancements<\/span><\/h2>\n

In Couchbase Server version 6.5, N1QL adds support for expression and subquery as right-hand side of an ANSI JOIN. Previously in version 5.5 and 6.0, the right-hand side of an ANSI JOIN must be a keyspace. An error is returned if the right-hand side of an ANSI JOIN is not a keyspace.<\/span><\/p>\n

The restriction for keyspace only exists in version 5.5 and 6.0, and it only applies to the right-hand side of an ANSI JOIN. The left-hand side of an ANSI JOIN can be a keyspace, an expression, a subquery, or another join\/nest\/unnest operation, as long as ANSI JOIN is not mixed with a non-ANSI join\/nest operation.<\/span><\/p>\n

Expression as right-hand side of an ANSI JOIN<\/span><\/h3>\n

An expression can now be used on the right-hand side of ANSI JOIN. For example:<\/span><\/p>\n

SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN\r\n  [ {\"destinationairport\": \"KEF\", \"sourceairport\": \"SFO\", \"type\": \"route\"},\r\n    {\"destinationairport\": \"KEF\", \"sourceairport\": \"LHR\", \"type\": \"route\"} ] AS route\r\n  ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Francisco\";<\/pre>\n
In this example, an explicit array of objects is specified as an expression on the right-hand side of an ANSI JOIN. Note that when an expression is used on the right-hand side of an ANSI JOIN, the expression must have an alias (\u201croute\u201d in the example above, although the AS keyword is optional).\u00a0<\/span><\/p>\n
When an expression is used on the right-hand side of an ANSI JOIN, an ExpressionScan operator is used to iterate through the result set of the expression in the explain plan:<\/span><\/p>\n
\"plan\": {\r\n    \"#operator\": \"Sequence\",\r\n    \"~children\": [\r\n        {\r\n            \"#operator\": \"IndexScan3\",\r\n            \"as\": \"airport\",\r\n            \"index\": \"ix_airport_city\",\r\n            \"index_id\": \"eee67e7e615a1b49\",\r\n            \"index_projection\": {\r\n                \"primary_key\": true\r\n            },\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\",\r\n            \"spans\": [\r\n                {\r\n                    \"exact\": true,\r\n                    \"range\": [\r\n                        {\r\n                            \"high\": \"\\\"San Francisco\\\"\",\r\n                            \"inclusion\": 3,\r\n                            \"low\": \"\\\"San Francisco\\\"\"\r\n                        }\r\n                    ]\r\n                }\r\n            ],\r\n            \"using\": \"gsi\"\r\n        },\r\n        {\r\n            \"#operator\": \"Fetch\",\r\n            \"as\": \"airport\",\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\"\r\n        },\r\n        {\r\n            \"#operator\": \"HashJoin\",\r\n            \"build_aliases\": [\r\n                \"route\"\r\n            ],\r\n            \"build_exprs\": [\r\n                \"(`route`.`sourceairport`)\"\r\n            ],\r\n            \"on_clause\": \"(((`airport`.`faa`) = (`route`.`sourceairport`)) and ((`route`.`type`) = \\\"route\\\"))\",\r\n            \"probe_exprs\": [\r\n                \"(`airport`.`faa`)\"\r\n            ],\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"ExpressionScan\",\r\n                        \"alias\": \"route\",\r\n                        \"expr\": \"[{\\\"destinationairport\\\": \\\"KEF\\\", \\\"sourceairport\\\": \\\"SFO\\\", \\\"type\\\": \\\"route\\\"}, {\\\"destinationairport\\\": \\\"KEF\\\", \\\"sourceairport\\\": \\\"LHR\\\", \\\"type\\\": \\\"route\\\"}]\",\r\n                        \"uncorrelated\": true\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Parallel\",\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"Filter\",\r\n                        \"condition\": \"(((`airport`.`type`) = \\\"airport\\\") and ((`airport`.`city`) = \\\"San Francisco\\\"))\"\r\n                    },\r\n                    {\r\n                        \"#operator\": \"InitialProject\",\r\n                        \"distinct\": true,\r\n                        \"result_terms\": [\r\n                            {\r\n\r\n                                \"expr\": \"(`route`.`destinationairport`)\"\r\n                            }\r\n                        ]\r\n                    },\r\n                    {\r\n                        \"#operator\": \"Distinct\"\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Distinct\"\r\n        }\r\n    ]\r\n},\r\n<\/pre>\n
 <\/p>\n
Subquery as right-hand side of an ANSI JOIN<\/span><\/h3>\n
A subquery can also be used on the right-hand side of ANSI JOIN. For example:<\/span><\/p>\n
SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN\r\n ( SELECT destinationairport, sourceairport\r\n   FROM `travel-sample` WHERE type = \"route\" ) AS route\r\n ON airport.faa = route.sourceairport\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Francisco\";\r\n<\/pre>\n
In this example, a subquery is used on the right-hand side of an ANSI JOIN. Similar to an expression, when a subquery is used on the right-hand side of an ANSI JOIN, the subquery must also have an alias (\u201croute\u201d in the example above, although the AS keyword is optional).<\/span><\/p>\n
When a subquery is used on the right-hand side of an ANSI JOIN, the explain shows the plan for the subquery itself:<\/span><\/p>\n
\"plan\": {\r\n    \"#operator\": \"Sequence\",\r\n    \"~children\": [\r\n        {\r\n            \"#operator\": \"IndexScan3\",\r\n            \"as\": \"airport\",\r\n            \"index\": \"ix_airport_city\",\r\n            \"index_id\": \"eee67e7e615a1b49\",\r\n            \"index_projection\": {\r\n                \"primary_key\": true\r\n            },\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\",\r\n            \"spans\": [\r\n                {\r\n                    \"exact\": true,\r\n                    \"range\": [\r\n                        {\r\n                            \"high\": \"\\\"San Francisco\\\"\",\r\n                            \"inclusion\": 3,\r\n                            \"low\": \"\\\"San Francisco\\\"\"\r\n                        }\r\n                    ]\r\n                }\r\n            ],\r\n            \"using\": \"gsi\"\r\n        },\r\n        {\r\n            \"#operator\": \"Fetch\",\r\n            \"as\": \"airport\",\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\"\r\n        },\r\n        {\r\n            \"#operator\": \"HashJoin\",\r\n            \"build_aliases\": [\r\n                \"route\"\r\n            ],\r\n            \"build_exprs\": [\r\n                \"(`route`.`sourceairport`)\"\r\n            ],\r\n            \"on_clause\": \"((`airport`.`faa`) = (`route`.`sourceairport`))\",\r\n            \"probe_exprs\": [\r\n                \"(`airport`.`faa`)\"\r\n            ],\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"Sequence\",\r\n                        \"~children\": [\r\n                            {\r\n                                \"#operator\": \"IndexScan3\",\r\n                                \"index\": \"ix_type\",\r\n                                \"index_id\": \"d925e49b3a11ae3d\",\r\n                                \"index_projection\": {\r\n                                    \"primary_key\": true\r\n                                },\r\n                                \"keyspace\": \"travel-sample\",\r\n                                \"namespace\": \"default\",\r\n                                \"spans\": [\r\n                                    {\r\n                                        \"exact\": true,\r\n                                        \"range\": [\r\n                                            {\r\n                                                \"high\": \"\\\"route\\\"\",\r\n                                                \"inclusion\": 3,\r\n                                                \"low\": \"\\\"route\\\"\"\r\n                                            }\r\n                                        ]\r\n                                    }\r\n                                ],\r\n                                \"using\": \"gsi\"\r\n                            },\r\n\r\n                            {\r\n                                \"#operator\": \"Fetch\",\r\n                                \"keyspace\": \"travel-sample\",\r\n                                \"namespace\": \"default\"\r\n                            },\r\n                            {\r\n                                \"#operator\": \"Parallel\",\r\n                                \"~child\": {\r\n                                    \"#operator\": \"Sequence\",\r\n                                    \"~children\": [\r\n                                        {\r\n                                            \"#operator\": \"Filter\",\r\n                                            \"condition\": \"((`travel-sample`.`type`) = \\\"route\\\")\"\r\n                                        },\r\n                                        {\r\n                                            \"#operator\": \"InitialProject\",\r\n                                            \"result_terms\": [\r\n                                                {\r\n                                                    \"expr\": \"(`travel-sample`.`destinationairport`)\"\r\n                                                },\r\n                                                {\r\n                                                    \"expr\": \"(`travel-sample`.`sourceairport`)\"\r\n                                                }\r\n                                            ]\r\n\r\n                                        }\r\n                                    ]\r\n                                }\r\n                            }\r\n                        ]\r\n                    },\r\n                    {\r\n                        \"#operator\": \"Alias\",\r\n                        \"as\": \"route\"\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Parallel\",\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"Filter\",\r\n                        \"condition\": \"(((`airport`.`type`) = \\\"airport\\\") and ((`airport`.`city`) = \\\"San Francisco\\\"))\"\r\n                    },\r\n                    {\r\n                        \"#operator\": \"InitialProject\",\r\n\r\n                        \"distinct\": true,\r\n                        \"result_terms\": [\r\n                            {\r\n                                \"expr\": \"(`route`.`destinationairport`)\"\r\n                            }\r\n                        ]\r\n                    },\r\n                    {\r\n                        \"#operator\": \"Distinct\"\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Distinct\"\r\n        }\r\n    ]\r\n},\r\n<\/pre>\n
When a subquery is used as right-hand side of an ANSI JOIN, it cannot be a correlated subquery. An error will be returned if a correlated subquery is used on the right-hand side of an ANSI JOIN.<\/p>\n
Hash join used when an expression or a subquery is on the right-hand side of an ANSI JOIN<\/span><\/h3>\n
When a keyspace is used on the right-hand side of an ANSI JOIN, nested-loop join is the default join method, and hash join is considered when USE HASH hint is specified on the right-hand side keyspace. In addition, an appropriate secondary index on the keyspace is required when nested-loop join is used. In contrast, when an expression or a subquery is used on the right-hand side of an ANSI JOIN, since there is no index on an expression or a subquery, it is more efficient to use a hash join to perform the join. A nested-loop join in this case is effectively a cartesian join, since the result set of the expression or subquery needs to be repeatedly scanned for each outer document. As a result, when an expression or a subquery is on the right-hand side of an ANSI JOIN, hash join is the default join method, even without USE HASH hint specified. If hash join cannot be used, e.g. there is no equality join predicate, or when USE NL hint is used, or when community edition is being used (hash join is only available in enterprise edition), then nested-loop join is considered. Looking at the explain files above for cases of both expression and subquery as right-hand side of an ANSI JOIN, you can find a HashJoin operator used in both explain files.<\/span><\/p>\n
When a keyspace is used on the right-hand side of an ANSI JOIN, a USE INDEX or USE KEYS hint can be specified on the keyspace. When an expression or a subquery is used on the right-hand side of an ANSI JOIN, you cannot specify a USE INDEX or USE KEYS hint on the expression or subquery, for obvious reasons. The only hint available in this case is a join hint (USE HASH or USE NL).<\/span><\/p>\n
Support for ANSI NEST<\/span><\/h3>\n
The same same enhancement applies to ANSI NEST as well, i.e., you can now use an expression or a subquery as the right-hand side of an ANSI NEST.<\/span><\/p>\n
 <\/p>\n
ANSI MERGE<\/span><\/h2>\n
A MERGE statement allows documents from the \u201csource\u201d to be merged into the \u201ctarget\u201d, i.e., when a match is found, the target document can be updated; when a match is not found, a document can be inserted into the target.<\/span><\/p>\n
The concept of a \u201cmatch\u201d is determined by the match condition. In previous versions of Couchbase N1QL only supports a \u201cmatch\u201d based on document key, i.e., the source must produce a document key for the target, to determine whether a match is found (whether the document with that document key already exists in the target). This is achieved by the ON KEYS clause in the MERGE statement syntax.<\/span><\/p>\n
The MERGE statement with the ON KEYS clause is referred to as look-up merge. This is similar to the join syntax before ANSI JOIN support, i.e., look-up join, which also requires joining on document key, and uses ON KEYS clause. Therefore a natural extension of the MERGE statement is to use ANSI MERGE syntax, i.e., use ON clause to specify the merge condition, such that an arbitrary expression can be used to determine whether a match exists or not. ANSI MERGE is supported in N1QL in Couchbase server version 6.5.<\/span><\/p>\n
Here is an example of an ANSI MERGE statement:<\/span><\/p>\n
MERGE INTO `travel-sample` AS route USING `travel-sample` AS airport \r\n  ON route.sourceairport = airport.faa AND airport.type = \"airport\"\r\n   AND route.type = \"route\"\r\nWHEN MATCHED THEN UPDATE SET route.old_equipment = route.equipment,\r\n                             route.equipment = \"797\", route.updated = true\r\n     WHERE route.airline = \"BA\" AND airport.country = \"France\"\r\n       AND CONTAINS(route.equipment, \"319\");\r\n<\/pre>\n
In this example the merge condition is specified using an ON clause. This is very similar to the ON clause of an ANSI JOIN. In fact internally the ANSI MERGE statement uses ANSI JOIN with the same ON clause to determine whether a match exists for any source document.<\/span><\/p>\n
The explain for the merge statement above:<\/span><\/p>\n
\"plan\": {\r\n    \"#operator\": \"Sequence\",\r\n    \"~children\": [\r\n        {\r\n            \"#operator\": \"IndexScan3\",\r\n            \"as\": \"airport\",\r\n            \"index\": \"ix_type\",\r\n            \"index_id\": \"d925e49b3a11ae3d\",\r\n            \"index_projection\": {\r\n                \"primary_key\": true\r\n            },\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\",\r\n            \"spans\": [\r\n                {\r\n                    \"exact\": true,\r\n                    \"range\": [\r\n                        {\r\n                            \"high\": \"\\\"airport\\\"\",\r\n                            \"inclusion\": 3,\r\n                            \"low\": \"\\\"airport\\\"\"\r\n                        }\r\n                    ]\r\n                }\r\n            ],\r\n            \"using\": \"gsi\"\r\n        },\r\n        {\r\n            \"#operator\": \"Fetch\",\r\n            \"as\": \"airport\",\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\"\r\n        },\r\n        {\r\n            \"#operator\": \"Parallel\",\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"NestedLoopJoin\",\r\n                        \"alias\": \"route\",\r\n                        \"on_clause\": \"((((`route`.`sourceairport`) = (`airport`.`faa`)) and ((`airport`.`type`) = \\\"airport\\\")) and ((`route`.`type`) = \\\"route\\\"))\",\r\n                        \"~child\": {\r\n                            \"#operator\": \"Sequence\",\r\n                            \"~children\": [\r\n                                {\r\n                                    \"#operator\": \"IndexScan3\",\r\n                                    \"as\": \"route\",\r\n                                    \"index\": \"ix_type\",\r\n                                    \"index_id\": \"d925e49b3a11ae3d\",\r\n                                    \"index_projection\": {\r\n                                        \"primary_key\": true\r\n                                    },\r\n                                    \"keyspace\": \"travel-sample\",\r\n                                    \"namespace\": \"default\",\r\n                                    \"nested_loop\": true,\r\n                                    \"spans\": [\r\n                                        {\r\n                                            \"exact\": true,\r\n                                            \"range\": [\r\n                                                {\r\n                                                    \"high\": \"\\\"route\\\"\",\r\n                                                    \"inclusion\": 3,\r\n                                                    \"low\": \"\\\"route\\\"\"\r\n                                                }\r\n                                            ]\r\n                                        }\r\n                                    ],\r\n                                    \"using\": \"gsi\"\r\n                                },\r\n                                {\r\n                                    \"#operator\": \"Fetch\",\r\n                                    \"as\": \"route\",\r\n                                    \"keyspace\": \"travel-sample\",\r\n                                    \"namespace\": \"default\",\r\n                                    \"nested_loop\": true\r\n                                }\r\n                            ]\r\n                        }\r\n                    },\r\n                    {\r\n                        \"#operator\": \"Merge\",\r\n                        \"as\": \"route\",\r\n                        \"keyspace\": \"travel-sample\",\r\n                        \"namespace\": \"default\",\r\n                        \"update\": {\r\n                            \"#operator\": \"Sequence\",\r\n                            \"~children\": [\r\n                                {\r\n                                    \"#operator\": \"Filter\",\r\n                                    \"condition\": \"((((`route`.`airline`) = \\\"BA\\\") and ((`airport`.`country`) = \\\"France\\\")) and contains((`route`.`equipment`), \\\"319\\\"))\"\r\n                                },\r\n                                {\r\n                                    \"#operator\": \"Clone\"\r\n                                },\r\n                                {\r\n                                    \"#operator\": \"Set\",\r\n                                    \"set_terms\": [\r\n                                        {\r\n                                            \"path\": \"(`route`.`old_equipment`)\",\r\n                                            \"value\": \"(`route`.`equipment`)\"\r\n                                        },\r\n                                        {\r\n                                            \"path\": \"(`route`.`equipment`)\",\r\n                                            \"value\": \"\\\"797\\\"\"\r\n                                        },\r\n                                        {\r\n                                            \"path\": \"(`route`.`updated`)\",\r\n                                            \"value\": \"true\"\r\n                                        }\r\n                                    ]\r\n                                },\r\n                                {\r\n                                    \"#operator\": \"SendUpdate\",\r\n                                    \"alias\": \"route\",\r\n                                    \"keyspace\": \"travel-sample\",\r\n                                    \"namespace\": \"default\"\r\n                                }\r\n                            ]\r\n                        }\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Discard\"\r\n        }\r\n    ]\r\n},\r\n<\/pre>\n
In the explain you can see a NestedLoopJoin operator which is used for the (internally transformed) ANSI JOIN operation between the source and the target.<\/span><\/p>\n
Join hint can be specified on the source:<\/p>\n
MERGE INTO `travel-sample` AS route USING `travel-sample` AS airport USE HASH(build)\r\n  ON route.sourceairport = airport.faa AND airport.type = \"airport\"\r\n   AND route.type = \"route\"\r\nWHEN MATCHED THEN UPDATE SET route.old_equipment = route.equipment,\r\n                             route.equipment = \"797\", route.updated = true\r\n     WHERE route.airline = \"BA\" AND airport.country = \"France\"\r\n       AND CONTAINS(route.equipment, \"319\");<\/pre>\n
In this example USE HASH hint is specified on airport.<\/p>\n
Handling multiple matches in ANSI MERGE<\/span><\/h3>\n
In look-up merge, since the merge condition is on document key, there is only a single potential target document match for each source document. In ANSI MERGE, since the merge condition is now an arbitrary expression, there could be potentially multiple target document matches for each source document. For each merge action specified, the merge action can only be executed at most once for each source document. In case of multiple matches, if a merge action is attempted a second time for the same source document, an error will be returned (error code 5320 or 5330). If such an error is encountered, the merge condition (ON clause) needs to be refined to avoid multiple matches for the same source document.<\/span><\/p>\n
INSERT action for ANSI MERGE<\/span><\/h3>\n
In look-up merge, if an INSERT action is specified, only the document being inserted is provided. Since look-up merge requires a match on document key, and the INSERT action is only triggered when no match is found (i.e., no document with that document key exists in target), the newly inserted document will simply use the same document key. For ANSI MERGE, this is no longer true, since a document key is no longer required to be the match condition. As a result, when an INSERT action is specified, a document key must also be specified as part of the INSERT action. For example:<\/span><\/p>\n
MERGE INTO `travel-sample` AS route USING `travel-sample` AS airport \r\n  ON route.sourceairport = airport.faa AND airport.type = \"airport\"\r\n   AND route.type = \"route\"\r\nWHEN NOT MATCHED THEN INSERT (KEY UUID(), VALUE {\"sourceairport\": airport.faa, \"targetairport\": \"SFO\", \"type\": \"route\"}) \r\n     WHERE airport.country = \"France\";\r\n<\/pre>\n
In comparison, here is the syntax for INSERT action for look-up merge:<\/span><\/p>\n
WHEN NOT MATCHED THEN INSERT <expr><\/pre>\n
where <expr> is the document to be inserted.<\/span><\/p>\n
Here is the syntax for INSERT action for ANSI MERGE:<\/span><\/p>\n
WHEN NOT MATCHED THEN INSERT (<key_expr>, <value_expr>)\r\nWHEN NOT MATCHED THEN INSERT (KEY <key_expr>, VALUE <value_expr>)<\/pre>\n
where <key_expr> specifies the document key for the newly inserted document, and <value_expr> specifies the new document. The two are separated by a comma.<\/span><\/p>\n
There are two forms that can be used, the KEY and VALUE keywords are optional. A new pair of parentheses is also required.<\/span><\/p>\n
The other merge actions (UPDATE action, DELETE action) remains the same between look-up merge and ANSI MERGE.<\/span><\/p>\n
 <\/p>\n
Summary<\/span><\/h2>\n
The enhancements for ANSI JOIN and support for ANSI MERGE in Couchbase 6.5 enhances ANSI compliance of N1QL, and makes N1QL more user friendly, especially for migration from a relational database.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction N1QL support for ANSI JOIN was first introduced in version 5.5. Currently there is a restriction in ANSI JOIN (version 5.5 and 6.0) in that the right-hand side of an ANSI JOIN must be a keyspace. This restriction will […]<\/p>\n","protected":false},"author":3021,"featured_media":13873,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[1816,1812],"tags":[],"ppma_author":[9065],"class_list":["post-8085","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-couchbase-server","category-n1ql-query"],"acf":[],"yoast_head":"\nANSI JOIN Enhancements and ANSI MERGE - The Couchbase Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ANSI JOIN Enhancements and ANSI MERGE\" \/>\n<meta property=\"og:description\" content=\"Introduction N1QL support for ANSI JOIN was first introduced in version 5.5. Currently there is a restriction in ANSI JOIN (version 5.5 and 6.0) in that the right-hand side of an ANSI JOIN must be a keyspace. This restriction will […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\" \/>\n<meta property=\"og:site_name\" content=\"The Couchbase Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-01-21T18:30:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-09-12T07:46:45+00:00\" \/>\n<meta name=\"author\" content=\"Bingjie Miao, Principal Software Engineer Couchbase\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bingjie Miao, Principal Software Engineer Couchbase\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\"},\"author\":{\"name\":\"Bingjie Miao, Senior Software Engineer, Couchbase\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/3a65595a167e56276aec1f708072c2e6\"},\"headline\":\"ANSI JOIN Enhancements and ANSI MERGE\",\"datePublished\":\"2020-01-21T18:30:47+00:00\",\"dateModified\":\"2024-09-12T07:46:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\"},\"wordCount\":1457,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"articleSection\":[\"Couchbase Server\",\"SQL++ \/ N1QL Query\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\",\"name\":\"ANSI JOIN Enhancements and ANSI MERGE - The Couchbase Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"datePublished\":\"2020-01-21T18:30:47+00:00\",\"dateModified\":\"2024-09-12T07:46:45+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"width\":1800,\"height\":630},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.couchbase.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ANSI JOIN Enhancements and ANSI MERGE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"name\":\"The Couchbase Blog\",\"description\":\"Couchbase, the NoSQL Database\",\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\",\"name\":\"The Couchbase Blog\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png\",\"width\":218,\"height\":34,\"caption\":\"The Couchbase Blog\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/3a65595a167e56276aec1f708072c2e6\",\"name\":\"Bingjie Miao, Senior Software Engineer, Couchbase\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/8834c93ec327fc373f7cf1896138b95b\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d0ea9ddc7d74a893baf9227b26296cccf1eed2fc94c0c94e7fb04485b7251025?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d0ea9ddc7d74a893baf9227b26296cccf1eed2fc94c0c94e7fb04485b7251025?s=96&d=mm&r=g\",\"caption\":\"Bingjie Miao, Senior Software Engineer, Couchbase\"},\"description\":\"Bingjie Miao is a principal software engineer at Couchbase. Bingjie has 20 years of experience in relational and NoSQL databases. His main area of expertise is query optimization and query execution.\",\"url\":\"https:\/\/www.couchbase.com\/blog\/author\/bingjie-miaocouchbase-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"ANSI JOIN Enhancements and ANSI MERGE - The Couchbase Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/","og_locale":"en_US","og_type":"article","og_title":"ANSI JOIN Enhancements and ANSI MERGE","og_description":"Introduction N1QL support for ANSI JOIN was first introduced in version 5.5. Currently there is a restriction in ANSI JOIN (version 5.5 and 6.0) in that the right-hand side of an ANSI JOIN must be a keyspace. This restriction will […]","og_url":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/","og_site_name":"The Couchbase Blog","article_published_time":"2020-01-21T18:30:47+00:00","article_modified_time":"2024-09-12T07:46:45+00:00","author":"Bingjie Miao, Principal Software Engineer Couchbase","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Bingjie Miao, Principal Software Engineer Couchbase","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#article","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/"},"author":{"name":"Bingjie Miao, Senior Software Engineer, Couchbase","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/3a65595a167e56276aec1f708072c2e6"},"headline":"ANSI JOIN Enhancements and ANSI MERGE","datePublished":"2020-01-21T18:30:47+00:00","dateModified":"2024-09-12T07:46:45+00:00","mainEntityOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/"},"wordCount":1457,"commentCount":0,"publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","articleSection":["Couchbase Server","SQL++ \/ N1QL Query"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/","url":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/","name":"ANSI JOIN Enhancements and ANSI MERGE - The Couchbase Blog","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","datePublished":"2020-01-21T18:30:47+00:00","dateModified":"2024-09-12T07:46:45+00:00","breadcrumb":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","width":1800,"height":630},{"@type":"BreadcrumbList","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.couchbase.com\/blog\/"},{"@type":"ListItem","position":2,"name":"ANSI JOIN Enhancements and ANSI MERGE"}]},{"@type":"WebSite","@id":"https:\/\/www.couchbase.com\/blog\/#website","url":"https:\/\/www.couchbase.com\/blog\/","name":"The Couchbase Blog","description":"Couchbase, the NoSQL Database","publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.couchbase.com\/blog\/#organization","name":"The Couchbase Blog","url":"https:\/\/www.couchbase.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","width":218,"height":34,"caption":"The Couchbase Blog"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/3a65595a167e56276aec1f708072c2e6","name":"Bingjie Miao, Senior Software Engineer, Couchbase","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/8834c93ec327fc373f7cf1896138b95b","url":"https:\/\/secure.gravatar.com\/avatar\/d0ea9ddc7d74a893baf9227b26296cccf1eed2fc94c0c94e7fb04485b7251025?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d0ea9ddc7d74a893baf9227b26296cccf1eed2fc94c0c94e7fb04485b7251025?s=96&d=mm&r=g","caption":"Bingjie Miao, Senior Software Engineer, Couchbase"},"description":"Bingjie Miao is a principal software engineer at Couchbase. Bingjie has 20 years of experience in relational and NoSQL databases. His main area of expertise is query optimization and query execution.","url":"https:\/\/www.couchbase.com\/blog\/author\/bingjie-miaocouchbase-com\/"}]}},"authors":[{"term_id":9065,"user_id":3021,"is_guest":0,"slug":"bingjie-miaocouchbase-com","display_name":"Bingjie Miao, Principal Software Engineer Couchbase","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/d0ea9ddc7d74a893baf9227b26296cccf1eed2fc94c0c94e7fb04485b7251025?s=96&d=mm&r=g","author_category":"","last_name":"Miao, Principal Software Engineer, Couchbase","first_name":"Bingjie","job_title":"","user_url":"","description":"Bingjie Miao is a principal software engineer at Couchbase. Bingjie has 20 years of experience in relational and NoSQL databases. His main area of expertise is query optimization and query execution."}],"_links":{"self":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/8085","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/users\/3021"}],"replies":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/comments?post=8085"}],"version-history":[{"count":0,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/8085\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media\/13873"}],"wp:attachment":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media?parent=8085"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/categories?post=8085"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/tags?post=8085"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=8085"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}