{"id":4705,"date":"2018-03-05T16:29:55","date_gmt":"2018-03-06T00:29:55","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=4705"},"modified":"2025-12-19T06:23:41","modified_gmt":"2025-12-19T14:23:41","slug":"ansi-join-support-n1ql","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/","title":{"rendered":"ANSI JOIN Support in N1QL"},"content":{"rendered":"

Overview<\/span><\/h2>\n

ANSI JOIN support is added in N1QL to Couchbase version 5.5. In previous versions of Couchbase, join support was limited to lookup join and index join, which works great when the document key from one side of the join can be produced by the other side of the join — that is, joining on a parent-child or child-parent relationship through a document key. <\/span><\/p>\n

Where this approach fall short is when the join is on arbitrary fields or expressions of fields, or when multiple join conditions are required. ANSI JOIN is a standardized join syntax widely used in relational databases. ANSI JOIN is much more flexible than lookup join and index join, allowing join to be done on arbitrary expressions on any fields in a document. This makes join operations much simpler and more powerful.<\/span><\/p>\n

ANSI JOIN syntax:<\/span><\/p>\n

lhs-expression [ join-type ] JOIN rhs-keyspace ON [ join-condition ]<\/span><\/p>\n

The left-hand side of the join, lhs-expression can be a keyspace, a N1QL expression, a subquery, or a previous join. The right-hand side of the join, rhs-keyspace, must be a keyspace. The ON-clause specifies the join condition, which can be any arbitrary expression, although it should contain predicates that allows an index scan on the right-hand side keyspace. Join-type can be INNER, LEFT OUTER, RIGHT OUTER. The INNER and OUTER keywords are optional, thus JOIN is the same as INNER JOIN, and LEFT JOIN is the same as LEFT OUTER JOIN. In relational databases join-type can also be FULL OUTER or CROSS, although FULL OUTER JOIN and CROSS JOIN are not supported currently in N1QL.<\/span><\/p>\n

Details of ANSI JOIN support<\/span><\/h2>\n

We\u2019ll use examples to show you new ways you can run queries using ANSI JOIN syntax, and how to transform your existing join queries in N1QL from lookup join or index join syntax into new ANSI JOIN syntax. It should be noted that lookup join and index join will continue to be supported in N1QL for backward compatibility, however you cannot mix lookup join or index join with the new ANSI JOIN syntax in the same query block, thus customers are encouraged to migrate to the new ANSI JOIN syntax.<\/span><\/p>\n

To follow along, install the travel-sample<\/a> sample bucket.<\/span><\/p>\n

Example 1: ANSI JOIN with arbitrary join condition<\/span><\/h4>\n

The join condition (ON-clause) for ANSI JOIN can be any expression, involving any fields of the documents being joined. For example:<\/span><\/p>\n

Required index:<\/span><\/p>\n

CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport) WHERE type = \"route\";<\/pre>\n
Optional index:<\/span><\/p>\n
CREATE INDEX airport_city_country ON `travel-sample`(city, country) WHERE type = \"airport\";<\/pre>\n
Query:<\/span><\/p>\n
SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN `travel-sample` route\r\n     ON airport.faa = route.sourceairport\r\n        AND route.type = \"route\"\r\nWHERE airport.type = \"airport\"\r\n  AND airport.city = \"San Francisco\"\r\n  AND airport.country = \"United States\";<\/pre>\n
In this query we are joining a field (\u201cfaa\u201d) from the airport document with a field (\u201csourceairport\u201d) from the route document (see the ON clause of the join). Such join is not possible with lookup join or index join in N1QL, since both requires joining on document key only.<\/span><\/p>\n
ANSI JOIN requires an appropriate index on the right-hand side keyspace (\u201cRequired index\u201d above). You can also create other indexes (e.g. \u201cOptional index\u201d above) to speed up your query. Without the optional index a primary scan will be used and query still works, however without the required index the query will not work and will return an error.<\/span><\/p>\n
Looking at the explain:<\/span><\/p>\n
\"plan\": {\r\n\u00a0\u00a0\u00a0\"#operator\": \"Sequence\",\r\n\u00a0\u00a0\u00a0\"~children\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"airport\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"airport_city_country\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"8e782fd1b124eec3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_projection\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"primary_key\": true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"\\\"San Francisco\\\"\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"San Francisco\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"\\\"United States\\\"\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"United States\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"using\": \"gsi\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Fetch\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"airport\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Parallel\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"~child\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Sequence\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"~children\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"NestedLoopJoin\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"alias\": \"route\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"on_clause\": \"(((`airport`.`faa`) = cover ((`route`.`sourceairport`))) and (cover ((`route`.`type`)) = \\\"route\\\"))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"~child\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Sequence\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"~children\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"route\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"covers\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`route`.`sourceairport`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`route`.`destinationairport`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((meta(`route`).`id`))\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"filter_covers\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`route`.`type`))\": \"route\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"route_airports\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"f1f4b9fbe85e45fd\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"nested_loop\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`airport`.`faa`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`airport`.`faa`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"using\": \"gsi\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Filter\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"condition\": \"((((`airport`.`type`) = \\\"airport\\\") and ((`airport`.`city`) = \\\"San Francisco\\\")) and ((`airport`.`country`) = \\\"United States\\\"))\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"InitialProject\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"distinct\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"result_terms\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"expr\": \"cover ((`route`.`destinationairport`))\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Distinct\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"FinalProject\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Distinct\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0]\r\n}\r\n<\/pre>\n
You will see a NestedLoopJoin operator is used to perform the join, and underneath that an IndexScan3 operator is used to access the right-hand side keyspace, \u201croute\u201d. The spans for the index scan looks like:<\/span><\/p>\n
\"spans\": [\r\n\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`airport`.`faa`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`airport`.`faa`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0}\r\n]<\/pre>\n
The index scan for the right-hand side keyspace (\u201croute\u201d) is using a field (\u201cfaa\u201d) from the left-hand side keyspace (\u201cairport\u201d) as search key. For each document from outer side keyspace \u201cairport\u201d the NestedLoopJoin operator performs an index scan on the inner side keyspace \u201croute\u201d to find matching documents, and produces join results. The join is performed in a nested-loop fashion, where the outer loop produces document from outer side keyspace, and a nested inner loop searches for matching inner side document for the current outer side document.<\/span><\/p>\n
The explain information can also be view graphically on Query Workbench, by clicking the Explain button followed by the Plan button:<\/p>\n\"\"\n
In this example the index scan on the right-hand side keyspace is a covered index scan. In case the index scan is not covered, a fetch operator will be following the index scan operator to fetch the document.<\/span><\/p>\n
It should be noted that nested-loop join requires an appropriate secondary index on the right-hand side keyspace of ANSI JOIN. Primary index is not considered for this purpose. If an appropriate secondary cannot be found, an error will be returned for the query.<\/span><\/p>\n
In addition, you might have noticed that the filter route.type = “route”<\/em>\u00a0<\/span>appears in the ON-clause as well. The ON-clause is different than the WHERE clause in that the ON-clause is evaluated as part of the join, while the WHERE clause is evaluated after all joins are done. This distinction is important, especially for outer joins. Therefore it is recommended that you include filters on the right-hand side keyspace for a join in the ON-clause as well, in addition to any join filters.<\/span><\/p>\n
Example 2: ANSI JOIN with multiple join conditions<\/span><\/h4>\n
While lookup join and index join only joins on a single join condition (equality of document key), the ON-clause of ANSI JOIN can contain multiple join conditions.<\/span><\/p>\n
Required index:<\/span><\/p>\n
CREATE INDEX landmark_city_country ON `travel-sample`(city, country) WHERE type = \"landmark\";<\/pre>\n
Optional index:<\/span><\/p>\n
CREATE INDEX hotel_title ON `travel-sample`(title) WHERE type = \"hotel\";\r\n<\/pre>\n
Query:<\/span><\/p>\n
SELECT hotel.name hotel_name, landmark.name landmark_name, landmark.activity\r\nFROM `travel-sample` hotel JOIN `travel-sample` landmark\r\n\u00a0\u00a0\u00a0\u00a0ON hotel.city = landmark.city AND hotel.country = landmark.country AND landmark.type = \"landmark\"\r\nWHERE hotel.type = \"hotel\" AND hotel.title like \"Yosemite%\" AND array_length(hotel.public_likes) > 5;<\/pre>\n
Looking at the explain, the index spans for the index (\u201clandmark_city_country\u201d) of the right-hand side keyspace (\u201clandmark\u201d) is:<\/span><\/p>\n
\"spans\": [\r\n\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`hotel`.`city`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`hotel`.`city`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`hotel`.`country`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`hotel`.`country`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0}\r\n]\r\n<\/pre>\n
Thus multiple join predicates can potentially generate multiple index search keys for the index scan of the inner side of a nested-loop join.<\/span><\/p>\n
Example 3: ANSI JOIN with complex join expressions<\/span><\/h4>\n
The join condition in the ON-clause can be complex join expression. For example, the \u201cairlineid\u201d field in \u201croute\u201d document corresponds to the document key for \u201cairline\u201d document, but it can also be constructed by concatenating \u201cairline_\u201d with the \u201cid\u201d field of the \u201cairline\u201d document.<\/span><\/p>\n
Required index:<\/span><\/p>\n
CREATE INDEX route_airlineid ON `travel-sample`(airlineid) WHERE type = \"route\";<\/pre>\n
Optional index:<\/span><\/p>\n
CREATE INDEX airline_name ON `travel-sample`(name) WHERE type = \"airline\";<\/pre>\n
Query:<\/span><\/p>\n
SELECT count(*)\r\nFROM `travel-sample` airline JOIN `travel-sample` route\r\n     ON route.airlineid = \"airline_\" || tostring(airline.id) AND route.type = \"route\"\r\nWHERE airline.type = \"airline\" AND airline.name = \"United Airlines\";<\/pre>\n
The explain contains the following index spans for the right-hand side keyspace(\u201croute\u201d):<\/span><\/p>\n
\"spans\": [\r\n\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(\\\"airline_\\\" || to_string((`airline`.`id`)))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(\\\"airline_\\\" || to_string((`airline`.`id`)))\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0}\r\n]<\/pre>\n
The expression will be evaluated at runtime to generate the search keys for the index scan on the inner side of nested-loop join.<\/span><\/p>\n
Example 4: ANSI JOIN with IN clause<\/span><\/h4>\n
The join condition does not need to be an equality predicate. An IN-clause can be used as a join condition.<\/span><\/p>\n
Required index:<\/span><\/p>\n
CREATE INDEX airport_faa_name ON `travel-sample`(faa, airportname) WHERE type = \"airport\";<\/pre>\n
Optional index:<\/span><\/p>\n
CREATE INDEX route_airline_distance ON `travel-sample`(airline, distance) WHERE type = \"route\";<\/pre>\n
Query:<\/span><\/p>\n
SELECT DISTINCT airport.airportname\r\nFROM `travel-sample` route JOIN `travel-sample` airport\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa IN [ route.sourceairport, route.destinationairport ] AND airport.type = \"airport\"\r\nWHERE route.type = \"route\" AND route.airline = \"F9\" AND route.distance > 3000;\r\n<\/pre>\n
The explain contains the following index spans for the right-hand side keyspace(\u201cairport\u201d):<\/p>\n
\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`route`.`sourceairport`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`route`.`sourceairport`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`route`.`destinationairport`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`route`.`destinationairport`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0}\r\n]\r\n<\/pre>\n
 <\/p>\n
Example 5: ANSI JOIN with OR clause<\/span><\/h4>\n
Similar to IN-clause, the join condition for an ANSI JOIN can also contain an OR-clause. Different arms of the OR-clause can potentially reference different fields of the right-hand side keyspace, as long as appropriate indexes exists.<\/span><\/p>\n
Required index (route_airports index same as example 1):<\/span><\/p>\n
CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport) WHERE type = \"route\";\r\nCREATE INDEX route_airports2 ON `travel-sample`(destinationairport, sourceairport) WHERE type = \"route\";<\/pre>\n
Optional index (same as example 1):<\/span><\/p>\n
CREATE INDEX airport_city_country ON `travel-sample`(city, country) WHERE type = \"airport\";<\/pre>\n
Query:<\/span><\/p>\n
SELECT count(*)\r\nFROM `travel-sample` airport JOIN `travel-sample` route\r\n\u00a0\u00a0\u00a0\u00a0ON (route.sourceairport = airport.faa OR route.destinationairport = airport.faa) AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"Denver\" AND airport.country = \"United States\";<\/pre>\n
The explain shows an UnionScan being used under NestedLoopJoin, to handle the OR-clause:<\/span><\/p>\n
\"#operator\": \"UnionScan\",\r\n\"scans\": [\r\n\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"route\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"route_airports\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"f1f4b9fbe85e45fd\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_projection\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"primary_key\": true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"nested_loop\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`airport`.`faa`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`airport`.`faa`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"using\": \"gsi\"\r\n\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"route\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"route_airports2\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"cdc9dca18c973bd3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_projection\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"primary_key\": true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"nested_loop\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`airport`.`faa`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`airport`.`faa`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"using\": \"gsi\"\r\n\u00a0\u00a0\u00a0}\r\n]<\/pre>\n
 <\/p>\n
Example 6: ANSI JOIN with hints<\/span><\/h4>\n
For lookup join and index join, hints can only be specified on the keyspace on the left-hand side of the join. For ANSI JOIN, hints can be specified on the right-hand side keyspace as well. Using the same query as example 1 (with addition of USE INDEX hint):<\/span><\/p>\n
SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN `travel-sample` route USE INDEX(route_airports)\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Francisco\" AND airport.country = \"United States\";<\/pre>\n
The USE INDEX hint limits the number of indexes the planner needs to consider for performing the join.<\/span><\/p>\n
Hints can also be specified on the left-hand side keyspace of ANSI JOIN.<\/span><\/p>\n
SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport USE INDEX(airport_city_country)\r\n\u00a0\u00a0JOIN `travel-sample` route USE INDEX(route_airports)\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Francisco\" AND airport.country = \"United States\";\r\n\r\n<\/pre>\n
 <\/p>\n
Example 7: ANSI LEFT OUTER JOIN<\/span><\/h4>\n
So far we\u2019ve been looking at inner joins. You can also perform LEFT OUTER JOIN by just including LEFT or LEFT OUTER keywords in front of JOIN keyword in join specification.<\/span><\/p>\n
Required index (same as example 1):<\/span><\/p>\n
CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport) WHERE type = \"route\";<\/pre>\n
Optional index (same as example 1):<\/span><\/p>\n
CREATE INDEX airport_city_country ON `travel-sample`(city, country) WHERE type = \"airport\";<\/pre>\n
Query:<\/span><\/p>\n
SELECT airport.airportname, route.airlineid\r\nFROM `travel-sample` airport LEFT JOIN `travel-sample` route\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"Denver\" AND airport.country = \"United States\";<\/pre>\n
The result set for this query contains all the joined results, as well as any left-hand side (\u201cairport\u201d) document that does not join with the right-hand side (\u201croute\u201d) document, according to semantics of LEFT OUTER JOIN. Thus you\u2019ll find results that just contain airport.airportname but not route.airlineid (which is missing). You can also select just the left-hand side (\u201cairport\u201d) document that does not join with right-hand side (\u201croute\u201d) document by adding a IS MISSING predicate on the right-hand side keyspace (\u201croute\u201d):<\/span><\/p>\n
SELECT airport.airportname, route.airlineid\r\nFROM `travel-sample` airport LEFT JOIN `travel-sample` route\r\n     ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"Denver\" AND airport.country = \"United States\"\r\n  AND route.airlineid IS MISSING;<\/pre>\n
 <\/p>\n
Example 8: ANSI RIGHT OUTER JOIN<\/span><\/h4>\n
ANSI RIGHT OUTER JOIN is similar to ANSI LEFT OUTER JOIN except we preserve the right-hand side document if no join occurs. We can modify the query in example 7 by switching the left-hand side and right-hand side keyspaces, and replacing LEFT keyword with RIGHT keyword:<\/span><\/p>\n
SELECT airport.airportname, route.airlineid\r\nFROM `travel-sample` route RIGHT JOIN `travel-sample` airport\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"Denver\" AND airport.country = \"United States\";<\/pre>\n
Note that although we switched airport and route in join specification, the filter on route (now the left-hand side keyspace) still appears in the ON-clause of the join, since route is still on the subservient side in this outer join.<\/span><\/p>\n
RIGHT OUTER JOIN is internally converted to LEFT OUTER JOIN.<\/span><\/p>\n
If a query contains multiple joins, a RIGHT OUTER JOIN can only be the first join specified. Since N1QL only support linear joins, i.e., the right-hand side of each join must be a single keyspace, if a RIGHT OUTER JOIN is not the first join specified, then after converting to LEFT OUTER JOIN, the right-hand side of the join now contains multiple keyspaces, which is not supported. If you specify RIGHT OUTER JOIN in any position other than the first join, a syntax error will be returned.<\/span><\/p>\n
Example 9: ANSI JOIN using Hash Join<\/span><\/h4>\n
N1QL supports two join methods for ANSI JOIN. The default join method for an ANSI JOIN is nested-loop join. The alternative is hash join. Hash join uses a hash table to match documents from both sides of the join. Hash join has a build side and a probe side, where each document from the build side is inserted into a hash table based on values of equi-join expression from the build side; subsequently each document from the probe side looks up from the hash table based on values of equi-join expression from the probe side. If a match is found then the join operation is performed.<\/span><\/p>\n
Compared with nested-loop join, hash join can be more efficient when the join is large, e.g., when there are tens of thousand or more documents from the left-hand side of the join (after applying filters). If using nested-loop join, then for each document from the left-hand side an index scan needs to be performed on the right-hand side index. As the number of documents from the left-hand side increases, nested-loop join becomes less efficient.<\/span><\/p>\n
For hash join, the smaller side of the join should be used for building the hash table, and the larger side of the join should be used for probing the hash table. It should be noted that hash join does require more memory than nested-loop join, since an in-memory hash table is required. The amount of memory required is proportional to the number of documents from the build side, as well as average size of each document.<\/span><\/p>\n
Hash join is supported in enterprise edition only<\/strong>. To use hash join, a USE HASH hint must be specified on the right-hand side keyspace of ANSI JOIN. Using the same query as example 1:<\/span><\/p>\n
SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN `travel-sample` route USE HASH(build)\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Jose\" AND airport.country = \"United States\";<\/pre>\n
The USE HASH(build) hint directs the N1QL planner to perform hash join for the ANSI JOIN specified, and the right-hand side keyspace (\u201croute\u201d) is used on the build side of the hash join. Looking at the explain, there is a HashJoin operator:<\/span><\/p>\n
{\r\n\u00a0\u00a0\u00a0\"#operator\": \"HashJoin\",\r\n\u00a0\u00a0\u00a0\"build_aliases\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"route\"\r\n\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\"build_exprs\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`route`.`sourceairport`))\"\r\n\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\"on_clause\": \"(((`airport`.`faa`) = cover ((`route`.`sourceairport`))) and (cover ((`route`.`type`)) = \\\"route\\\"))\",\r\n\u00a0\u00a0\u00a0\"probe_exprs\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"(`airport`.`faa`)\"\r\n\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\"~child\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Sequence\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"~children\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"route\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"covers\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`route`.`sourceairport`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`route`.`destinationairport`))\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((meta(`route`).`id`))\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"filter_covers\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`route`.`type`))\": \"route\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"route_airports\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"f1f4b9fbe85e45fd\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 0,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"null\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"using\": \"gsi\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0}\r\n}\r\n<\/pre>\n
The child operator (\u201c~child\u201d) for a HashJoin operator is always the build side of the hash join. For this query, it\u2019s an index scan on the right-hand side keyspace \u201croute\u201d.<\/span><\/p>\n
Note that for accessing the \u201croute\u201d document we can no longer use information from the left-hand side keyspace (\u201cairport\u201d) for index search key (look at the \u201cspans\u201d information in the explain section above). Unlike nested-loop join, the index scan on \u201croute\u201d is no longer tied to an individual document from the left-hand side, and thus no value from the \u201cairport\u201d document can be used as a search key for the index scan on \u201croute\u201d.<\/span><\/p>\n
The USE HASH(build) hint used in the query above directs the planner to use the right-hand side keyspace as the build side of the hash join. You can also specify USE HASH(probe) hint to direct the planner to use the right-hand side keyspace as the probe side of the hash join.<\/span><\/p>\n
SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN `travel-sample` route USE HASH(probe)\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Jose\" AND airport.country = \"United States\";<\/pre>\n
Looking at the explain, you\u2019ll find the HashJoin operator:<\/span><\/p>\n
{\r\n\u00a0\u00a0\u00a0\"#operator\": \"HashJoin\",\r\n\u00a0\u00a0\u00a0\"build_aliases\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"airport\"\r\n\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\"build_exprs\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"(`airport`.`faa`)\"\r\n\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\"on_clause\": \"(((`airport`.`faa`) = cover ((`route`.`sourceairport`))) and (cover ((`route`.`type`)) = \\\"route\\\"))\",\r\n\u00a0\u00a0\u00a0\"probe_exprs\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`route`.`sourceairport`))\"\r\n\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\"~child\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Sequence\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"~children\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IntersectScan\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"scans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"airport\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"airport_city_country\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"8e782fd1b124eec3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_projection\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"primary_key\": true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"\\\"San Jose\\\"\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"San Jose\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"\\\"United States\\\"\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"\\\"United States\\\"\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"using\": \"gsi\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"airport\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"airport_faa\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"c302afbf811470f5\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_projection\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"primary_key\": true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 0,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"null\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"using\": \"gsi\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Fetch\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"airport\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"travel-sample\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0}\r\n}<\/pre>\n
The child operator (\u201c~child\u201d) for HashJoin is an intersect index scan on the left-hand side keyspace of the ANSI JOIN, \u201cairport\u201d, followed by a fetch operator.<\/span><\/p>\n
The USE HASH hint can only be specified on the right-hand side keyspace in an ANSI JOIN. Therefore depending on whether you want the right-hand side keyspace to be the build side or the probe side of a hash join, a USE HASH(build) or USE HASH(probe) hint should be specified on the right-hand side keyspace.<\/span><\/p>\n
Hash join is only considered when USE HASH(build) or USE HASH(probe) hint is specified. Hash join requires equality join predicates to work. Nested-loop join requires an appropriate secondary index on the right-hand side keyspace, hash join does not (a primary index scan is an option for hash join). However, hash join does require more memory than nested-loop join since an in-memory hash table is required for hash join to work. In addition, hash join is considered a \u201cblocking\u201d operation, meaning the query engine must finish building the hash table before it can produce the first join result, thus for queries needing only the first few results quickly (e.g. with a LIMIT clause) hash join may not be the best fit.<\/span><\/p>\n
If a USE HASH hint is specified, but a hash join cannot be generated successfully (e.g., lack of equality join predicates), then a nested-loop join will be considered.<\/span><\/p>\n
Example 10: ANSI JOIN with multiple hints<\/span><\/h4>\n
You can now specify multiple hints for a keyspace on the right-hand side of an ANSI JOIN. For example, USE HASH hint can be used together with USE INDEX hint.<\/span><\/p>\n
SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN `travel-sample` route USE HASH(probe) INDEX(route_airports)\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Jose\" AND airport.country = \"United States\";<\/pre>\n
Note when multiple hints are used together, you only need to specify the \u201cUSE\u201d keyword once, as in the example above.<\/span><\/p>\n
USE HASH hint can also be combined with USE KEYS hint.<\/span><\/p>\n
Example 11: ANSI JOIN with multiple joins<\/span><\/h4>\n
ANSI JOIN can be chained together. For example:<\/span><\/p>\n
Required indexes (route_airports index same as example 1):<\/span><\/p>\n
CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport) WHERE type = \"route\";\r\nCREATE INDEX airline_iata ON `travel-sample`(iata) WHERE type = \"airline\";<\/pre>\n
Optional index (same as example 1):<\/span><\/p>\n
CREATE INDEX airport_city_country ON `travel-sample`(city, country) WHERE type = \"airport\";<\/pre>\n
Query:<\/span><\/p>\n
SELECT DISTINCT airline.name\r\nFROM `travel-sample` airport INNER JOIN `travel-sample` route\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa = route.sourceairport AND route.type = \"route\"\r\n\u00a0\u00a0INNER JOIN `travel-sample` airline\r\n\u00a0\u00a0\u00a0\u00a0ON route.airline = airline.iata AND airline.type = \"airline\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Jose\"\r\n\u00a0AND airport.country = \"United States\";<\/pre>\n
Since there is no USE HASH hint specified in the query the explain should show two NestedLoopJoin operators.<\/span><\/p>\n
You can mix hash join with nested-loop join by adding USE HASH hint to any of the joins in a chain of ANSI JOINs.<\/span><\/p>\n
SELECT DISTINCT airline.name\r\nFROM `travel-sample` airport INNER JOIN `travel-sample` route\r\n     ON airport.faa = route.sourceairport AND route.type = \"route\"\r\n   INNER JOIN `travel-sample` airline USE HASH(build)\r\n     ON route.airline = airline.iata AND airline.type = \"airline\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Jose\"\r\n  AND airport.country = \"United States\";<\/pre>\n
or<\/span><\/p>\n
SELECT DISTINCT airline.name\r\nFROM `travel-sample` airport INNER JOIN `travel-sample` route USE HASH(probe)\r\n\u00a0\u00a0\u00a0\u00a0ON airport.faa = route.sourceairport AND route.type = \"route\"\r\n\u00a0\u00a0INNER JOIN `travel-sample` airline\r\n\u00a0\u00a0\u00a0\u00a0ON route.airline = airline.iata AND airline.type = \"airline\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Jose\"\r\n\u00a0AND airport.country = \"United States\";<\/pre>\n
The visual explain for the last query is follows:<\/span><\/p>\n
\"\"<\/p>\n
As mentioned before, N1QL only supports linear joins, i.e., the right-hand side of each join must be a keyspace.<\/span><\/p>\n
Example 12: ANSI JOIN involving right-hand side arrays<\/span><\/h4>\n
Although ANSI JOIN comes from SQL standard, since Couchbase N1QL handles JSON documents and array is an important aspect of JSON, we extended ANSI JOIN support to arrays as well.<\/span><\/p>\n
For examples in array handling please create a bucket \u201cdefault\u201d and insert the following documents:<\/span><\/p>\n
INSERT INTO default (KEY,VALUE) VALUES(\"test11_ansijoin\", {\"c11\": 1, \"c12\": 10, \"a11\": [ 1, 2, 3, 4 ], \"type\": \"left\"}),\r\n                                VALUES(\"test12_ansijoin\", {\"c11\": 2, \"c12\": 20, \"a11\": [ 3, 3, 5, 10 ], \"type\": \"left\"}),\r\n                                VALUES(\"test13_ansijoin\", {\"c11\": 3, \"c12\": 30, \"a11\": [ 3, 4, 20, 40 ], \"type\": \"left\"}),\r\n                                VALUES(\"test14_ansijoin\", {\"c11\": 4, \"c12\": 40, \"a11\": [ 30, 30, 30 ], \"type\": \"left\"});\r\nINSERT INTO default (KEY,VALUE) VALUES(\"test21_ansijoin\", {\"c21\": 1, \"c22\": 10, \"a21\": [ 1, 10, 20], \"a22\": [ 1, 2, 3, 4 ], \"type\": \"right\"}),\r\n                                VALUES(\"test22_ansijoin\", {\"c21\": 2, \"c22\": 20, \"a21\": [ 2, 3, 30], \"a22\": [ 3, 5, 10, 3 ], \"type\": \"right\"}),\r\n                                VALUES(\"test23_ansijoin\", {\"c21\": 2, \"c22\": 21, \"a21\": [ 2, 20, 30], \"a22\": [ 3, 3, 5, 10 ], \"type\": \"right\"}),\r\n                                VALUES(\"test24_ansijoin\", {\"c21\": 3, \"c22\": 30, \"a21\": [ 3, 10, 30], \"a22\": [ 3, 4, 20, 40 ], \"type\": \"right\"}),\r\n                                VALUES(\"test25_ansijoin\", {\"c21\": 3, \"c22\": 31, \"a21\": [ 3, 20, 40], \"a22\": [ 4, 3, 40, 20 ], \"type\": \"right\"}),\r\n                                VALUES(\"test26_ansijoin\", {\"c21\": 3, \"c22\": 32, \"a21\": [ 4, 14, 24], \"a22\": [ 40, 20, 4, 3 ], \"type\": \"right\"}),\r\n                                VALUES(\"test27_ansijoin\", {\"c21\": 5, \"c22\": 50, \"a21\": [ 5, 15, 25], \"a22\": [ 1, 2, 3, 4 ], \"type\": \"right\"}),\r\n                                VALUES(\"test28_ansijoin\", {\"c21\": 6, \"c22\": 60, \"a21\": [ 6, 16, 26], \"a22\": [ 3, 3, 5, 10 ], \"type\": \"right\"}),\r\n                                VALUES(\"test29_ansijoin\", {\"c21\": 7, \"c22\": 70, \"a21\": [ 7, 17, 27], \"a22\": [ 30, 30, 30 ], \"type\": \"right\"}),\r\n                                VALUES(\"test30_ansijoin\", {\"c21\": 8, \"c22\": 80, \"a21\": [ 8, 18, 28], \"a22\": [ 30, 30, 30 ], \"type\": \"right\"});<\/pre>\n
Then create the following indexes:<\/span><\/p>\n
CREATE INDEX default_ix_left on default(c11, DISTINCT a11) WHERE type = \"left\";\r\nCREATE INDEX default_ix_right on default(c21, DISTINCT a21) WHERE type = \"right\";<\/pre>\n
When the join predicate involves an array on the right-hand side of ANSI JOIN, you need to create an array index on the right-hand side keyspace.<\/span><\/p>\n
Query:<\/span><\/p>\n
SELECT b1.c11, b2.c21, b2.c22\r\nFROM default b1 JOIN default b2\r\n\u00a0\u00a0\u00a0\u00a0ON b2.c21 = b1.c11 AND ANY v IN b2.a21 SATISFIES v = b1.c12 END AND b2.type = \"right\"\r\nWHERE b1.type = \"left\";<\/pre>\n
Note that part of the join condition is an ANY clause which specifies that the left-hand side field b1.c12 can match any element of the right-hand side array b2.a21. For this join to work properly, we need an array index on b2.a21, e.g., default_ix_right index created above.<\/span><\/p>\n
The explain plan shows a NestedLoopJoin, with the child operator being a distinct scan on the array index default_ix_right.<\/span><\/p>\n
{\r\n\u00a0\u00a0\u00a0\"#operator\": \"NestedLoopJoin\",\r\n\u00a0\u00a0\u00a0\"alias\": \"b2\",\r\n\u00a0\u00a0\u00a0\"on_clause\": \"((((`b2`.`c21`) = (`b1`.`c11`)) and any `v` in (`b2`.`a21`) satisfies (`v` = (`b1`.`c12`)) end) and ((`b2`.`type`) = \\\"right\\\"))\",\r\n\u00a0\u00a0\u00a0\"~child\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Sequence\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"~children\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"DistinctScan\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"scan\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"IndexScan3\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"b2\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index\": \"default_ix_right\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_id\": \"ef4e7fa33f33dce\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"index_projection\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"primary_key\": true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"nested_loop\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"spans\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"exact\": true,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"range\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`b1`.`c11`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`b1`.`c11`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"high\": \"(`b1`.`c12`)\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"inclusion\": 3,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"low\": \"(`b1`.`c12`)\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"using\": \"gsi\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"#operator\": \"Fetch\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"as\": \"b2\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"keyspace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"namespace\": \"default\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"nested_loop\": true\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0}\r\n}<\/pre>\n
 <\/p>\n
Example 13: ANSI JOIN involving left-hand side arrays<\/span><\/h4>\n
If ANSI JOIN involves an array on the left-hand side, then there are two options for performing the join.<\/span><\/p>\n
Option 1: use UNNEST<\/span><\/h5>\n
Use UNNEST clause to flatten the left-hand side array first before performing the join.<\/span><\/p>\n
SELECT b1.c11, b2.c21, b2.c22\r\nFROM default b1 UNNEST b1.a11 AS ba1\r\n\u00a0\u00a0\u00a0\u00a0JOIN default b2 ON ba1 = b2.c21 AND b2.type = \"right\"\r\nWHERE b1.c11 = 2 AND b1.type = \"left\";<\/pre>\n
After the UNNEST the array becomes individual fields, and the subsequent join is just like a \u201cregular\u201d ANSI JOIN with fields from both sides.<\/span><\/p>\n
Option 2: use IN-clause<\/span><\/h5>\n
Alternatively, use IN-clause as join condition.<\/span><\/p>\n
SELECT b1.c11, b2.c21, b2.c22\r\nFROM default b1 JOIN default b2\r\n\u00a0\u00a0\u00a0\u00a0ON b2.c21 IN b1.a11 AND b2.type = \"right\"\r\nWHERE b1.c11 = 2 AND b1.type = \"left\";<\/pre>\n
The IN-clause is satisfied when any element of the array on the left-hand side keyspace (\u201cb1.a11\u201d) matches the right-hand side field (\u201cb2.c21\u201d).<\/span><\/p>\n
Note that there is a semantics difference between the two options. When there are duplicates in the array, the UNNEST option does not care about duplicates and will flatten the left-hand side documents to as many documents as number of elements in the array, thus may produce duplicated results; the IN-clause option will not produce duplicated results if there are duplicated elements in the array. In addition, when LEFT OUTER JOIN is performed, there may be a different number of preserved documents due to the flattening of the array with the UNNEST option. Thus the user is advised to pick the option that reflect the semantics needed for the query.<\/span><\/p>\n
Example 14: ANSI JOIN involving arrays on both sides<\/span><\/h4>\n
Although uncommon, it is possible to perform an ANSI JOIN when both sides of the join are arrays. In such cases, you can use a combination of the techniques described above. Use array index to handle array on the right-hand side, and use either UNNEST option or IN-clause option to handle array on the left-hand side.<\/span><\/p>\n
Option 1: use UNNEST clause<\/span><\/h5>\n
SELECT b1.c11, b2.c21, b2.c22\r\nFROM default b1 UNNEST b1.a11 AS ba1\r\n\u00a0\u00a0\u00a0\u00a0JOIN default b2 ON b2.c21 = b1.c11 AND ANY v IN b2.a21 SATISFIES v = ba1 END AND b2.type = \"right\"\r\nWHERE b1.type = \"left\";<\/pre>\n
Option 2: use IN-clause<\/span><\/h5>\n
SELECT b1.c11, b2.c21, b2.c22\r\nFROM default b1 JOIN default b2\r\n\u00a0\u00a0\u00a0\u00a0ON b2.c21 = b1.c11 AND ANY v IN b2.a21 SATISFIES v IN b1.a11 END AND b2.type = \"right\"\r\nWHERE b1.type = \"left\";<\/pre>\n
Again the two options are not semantically identical, and may give different results. Pick the option that reflects the semantics desired.<\/span><\/p>\n
Example 15: lookup join migration<\/span><\/h4>\n
N1QL will continue to support lookup join and index join for backward compatibility, however, you cannot mix ANSI JOIN with lookup join or index join in the same query. You can convert your existing queries from using lookup join and index join to the ANSI JOIN syntax. This example shows you how to convert a lookup join into ANSI JOIN syntax.<\/span><\/p>\n
Create the following index to speed up the query (same as example 1):<\/span><\/p>\n
CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport) WHERE type = \"route\";<\/pre>\n
This is a query using lookup join syntax (note the ON KEYS clause):<\/span><\/p>\n
SELECT airline.name\r\nFROM `travel-sample` route JOIN `travel-sample` airline\r\n\u00a0\u00a0\u00a0\u00a0ON KEYS route.airlineid\r\nWHERE route.type = \"route\" AND route.sourceairport = \"SFO\" AND route.destinationairport = \"JFK\";<\/pre>\n
In lookup join the left-hand side of the join (\u201croute\u201d) needs to produce document keys for the right-hand side of the join (\u201cairline\u201d), this is achieved by the ON KEYS clause. The join condition (which is implied from the syntax) is route.airlineid = meta(airline).id<\/em>, thus the same query can be specified using ANSI JOIN syntax:<\/span><\/p>\n
SELECT airline.name\r\nFROM `travel-sample` route JOIN `travel-sample` airline\r\n\u00a0\u00a0\u00a0\u00a0ON route.airlineid = meta(airline).id\r\nWHERE route.type = \"route\" AND route.sourceairport = \"SFO\" AND route.destinationairport = \"JFK\";<\/pre>\n
In this example the ON KEYS clause contain a single document key. It\u2019s possible for the ON KEYS clause to contain an array of document keys, in which case the converted ON clause will be in the form of an IN clause instead of an equality clause. Let\u2019s assume each route document has an array of document keys for airline, then the original ON KEYS clause:<\/span>\u00a0\u00a0<\/b><\/p>\n
    ON KEYS route.airlineids<\/pre>\n
can be converted to:<\/span><\/p>\n
\u00a0\u00a0\u00a0\u00a0ON meta(airline).id IN route.airlineids<\/pre>\n
 <\/p>\n
Example 16: index join migration<\/span><\/h4>\n
This example shows you how to convert an index join into ANSI JOIN syntax.<\/span><\/p>\n
Required index (same as example 3):<\/span><\/p>\n
CREATE INDEX route_airlineid ON `travel-sample`(airlineid) WHERE type = \"route\";<\/pre>\n
Optional index (same as example 3):<\/span><\/p>\n
CREATE INDEX airline_name ON `travel-sample`(name) WHERE type = \"airline\";<\/pre>\n
Query using index join syntax (note the ON KEY \u2026 FOR \u2026 clause):<\/span><\/p>\n
SELECT count(*)\r\nFROM `travel-sample` airline JOIN `travel-sample` route\r\n\u00a0\u00a0\u00a0\u00a0ON KEY route.airlineid FOR airline\r\nWHERE airline.type = \"airline\" AND route.type = \"route\" AND airline.name = \"United Airlines\";<\/pre>\n
In index join the document key for left-hand side (\u201cairline\u201d) is used to probe an index on an expression (\u201croute.airlineid\u201d which appears in the ON KEY clause) from the right-hand side (\u201croute\u201d) that corresponds to the document key for the left-hand side (\u201cairline\u201d which appears in the FOR clause). The join condition (implied from syntax) is route.airlineid = meta(airline).id<\/em>, thus the same query can be specified using ANSI JOIN syntax:<\/span><\/p>\n
SELECT count(*)\r\nFROM `travel-sample` airline JOIN `travel-sample` route\r\n\u00a0\u00a0\u00a0\u00a0ON route.airlineid = meta(airline).id\r\nWHERE airline.type = \"airline\" AND route.type = \"route\" AND airline.name = \"United Airlines\";<\/pre>\n
 <\/p>\n
Example 17: ANSI NEST<\/span><\/h4>\n
Couchbase N1QL supports NEST operation. Previously NEST can be done using lookup nest or index nest, similar to lookup join and index join, respectively. With ANSI JOIN support, NEST operation can also be done using similar syntax, i.e., using ON clause instead of ON KEYS (lookup nest) or ON KEY \u2026 FOR \u2026 (index nest) clauses. This new variant is referred to as ANSI NEST.<\/span><\/p>\n
Required index (route_airports index same as example 1, route_airline_distance index same as example 4):<\/span><\/p>\n
CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport) WHERE type = \"route\";\r\nCREATE INDEX route_airline_distance ON `travel-sample`(airline, distance) WHERE type = \"route\";<\/pre>\n
Optional index:<\/span><\/p>\n
CREATE INDEX airline_country_iata_name ON `travel-sample`(country, iata, name) WHERE type = \"airline\";<\/pre>\n
Query:<\/span><\/p>\n
SELECT airline.name, ARRAY {\"destination\": r.destinationairport} FOR r in route END as destinations\r\nFROM `travel-sample` airline NEST `travel-sample` route\r\n\u00a0\u00a0\u00a0\u00a0ON airline.iata = route.airline AND route.type = \"route\" AND route.sourceairport = \"SFO\"\r\nWHERE airline.type = \"airline\" AND airline.country = \"United States\";<\/pre>\n
As you can see the syntax for ANSI NEST is very similar to that of ANSI JOIN. There is one peculiar property for nest though. By definition the nest operation creates an array of all matching right-hand side document for each left-hand side document, which means the reference to the right-hand side keyspace, \u201croute\u201d in this query, has different meaning depending on where the reference is. The ON-clause is evaluated as part of the NEST operation, and thus references to \u201croute\u201d is referencing a single document. In contrast, references in the projection clause, or the WHERE clause, are evaluated after the NEST operation, and thus references to \u201croute\u201d means the nested array, thus it should be treated as an array. Notice the projection clause of the query above has an ARRAY construct with a FOR clause to access each individual document within the array (i.e., the reference to “route” is now in an array context).<\/span><\/p>\n
Summary<\/span><\/h2>\n
ANSI JOIN provides much more flexibility in join operations in Couchbase N1QL, compared to previously supported lookup join and index join, both of which require joining on document key only. The examples above show various ways you can use ANSI JOIN in queries. Since ANSI JOIN is widely used in the relational world, the support for ANSI JOIN in Couchbase N1QL should make it much easier to migrate applications from a relational database to Couchbase N1QL.<\/span><\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Overview ANSI JOIN support is added in N1QL to Couchbase version 5.5. In previous versions of Couchbase, join support was limited to lookup join and index join, which works great when the document key from one side of the join […]<\/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":[1815,1816,1812],"tags":[2182],"ppma_author":[9065],"class_list":["post-4705","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-best-practices-and-tutorials","category-couchbase-server","category-n1ql-query","tag-devbuild"],"acf":[],"yoast_head":"\nANSI JOIN Support in N1QL: Overview + Details | Couchbase<\/title>\n<meta name=\"description\" content=\"Find out why ANSI JOIN is more flexible than lookup join, and index join, allowing join to be done on arbitrary expressions on any fields in a document.\" \/>\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-support-n1ql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ANSI JOIN Support in N1QL\" \/>\n<meta property=\"og:description\" content=\"Find out why ANSI JOIN is more flexible than lookup join, and index join, allowing join to be done on arbitrary expressions on any fields in a document.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/\" \/>\n<meta property=\"og:site_name\" content=\"The Couchbase Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-06T00:29:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-12-19T14:23:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/03\/ANSI-JOIN-Visual-Explain-e1521063082771.png\" \/>\n\t<meta property=\"og:image:width\" content=\"2040\" \/>\n\t<meta property=\"og:image:height\" content=\"1457\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\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=\"16 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-support-n1ql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/\"},\"author\":{\"name\":\"Bingjie Miao, Senior Software Engineer, Couchbase\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/3a65595a167e56276aec1f708072c2e6\"},\"headline\":\"ANSI JOIN Support in N1QL\",\"datePublished\":\"2018-03-06T00:29:55+00:00\",\"dateModified\":\"2025-12-19T14:23:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/\"},\"wordCount\":3565,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"keywords\":[\"devbuild\"],\"articleSection\":[\"Best Practices and Tutorials\",\"Couchbase Server\",\"SQL++ \/ N1QL Query\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/\",\"name\":\"ANSI JOIN Support in N1QL: Overview + Details | Couchbase\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"datePublished\":\"2018-03-06T00:29:55+00:00\",\"dateModified\":\"2025-12-19T14:23:41+00:00\",\"description\":\"Find out why ANSI JOIN is more flexible than lookup join, and index join, allowing join to be done on arbitrary expressions on any fields in a document.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#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-support-n1ql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.couchbase.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ANSI JOIN Support in N1QL\"}]},{\"@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 Support in N1QL: Overview + Details | Couchbase","description":"Find out why ANSI JOIN is more flexible than lookup join, and index join, allowing join to be done on arbitrary expressions on any fields in a document.","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-support-n1ql\/","og_locale":"en_US","og_type":"article","og_title":"ANSI JOIN Support in N1QL","og_description":"Find out why ANSI JOIN is more flexible than lookup join, and index join, allowing join to be done on arbitrary expressions on any fields in a document.","og_url":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/","og_site_name":"The Couchbase Blog","article_published_time":"2018-03-06T00:29:55+00:00","article_modified_time":"2025-12-19T14:23:41+00:00","og_image":[{"width":2040,"height":1457,"url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/03\/ANSI-JOIN-Visual-Explain-e1521063082771.png","type":"image\/png"}],"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":"16 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#article","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/"},"author":{"name":"Bingjie Miao, Senior Software Engineer, Couchbase","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/3a65595a167e56276aec1f708072c2e6"},"headline":"ANSI JOIN Support in N1QL","datePublished":"2018-03-06T00:29:55+00:00","dateModified":"2025-12-19T14:23:41+00:00","mainEntityOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/"},"wordCount":3565,"commentCount":2,"publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","keywords":["devbuild"],"articleSection":["Best Practices and Tutorials","Couchbase Server","SQL++ \/ N1QL Query"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/","url":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/","name":"ANSI JOIN Support in N1QL: Overview + Details | Couchbase","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#primaryimage"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","datePublished":"2018-03-06T00:29:55+00:00","dateModified":"2025-12-19T14:23:41+00:00","description":"Find out why ANSI JOIN is more flexible than lookup join, and index join, allowing join to be done on arbitrary expressions on any fields in a document.","breadcrumb":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-support-n1ql\/#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-support-n1ql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.couchbase.com\/blog\/"},{"@type":"ListItem","position":2,"name":"ANSI JOIN Support in N1QL"}]},{"@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\/4705","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=4705"}],"version-history":[{"count":0,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/4705\/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=4705"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/categories?post=4705"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/tags?post=4705"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=4705"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}