ANSI JOINs in N1QL

概述

在Couchbase 5.5版本中添加了N1QL的ANSI JOIN支持。以前版本的Couchbase只支持lookup join(查找连接)和index join(索引连接)。当连接的一端的文档键可以由连接的另一端生成时，查找连接和索引连接就会工作得很好。即：通过文档键连接parent-child（父子）或child-parent（子父）关系。当连接位于字段的任意字段或表达式上，或者当需要多个连接条件时，它们就会出现不足。ANSI JOIN是一种在关系数据库中广泛使用的标准化连接语法。ANSI JOIN比查找连接和索引连接灵活得多，允许在文档中的任何字段上的任意表达式上执行连接，这使得连接操作更简单、更强大。

ANSI JOIN 语法:

lhs-expression [ join-type ] JOIN rhs-keyspace ON [ join-condition ]

连接的左侧，lhs表达式可以是一个keyspace，一个N1QL表达式，一个子查询，或者一个之前的连接。

连接的右边，rhs-keyspace，必须是一个keyspace。on子句指定了连接条件，它可以是任意表达式，尽管它应该包含允许在右边键空间上进行索引扫描的谓词。JOIN -type(连接类型)可以是内(INNER)、左外(LEFT OUTER)、右外(RIGHT OUTER)。内部(INNER)和外部(OUTER)关键字是可选的，因此连接(JOIN)与内部连接(INNER JOIN)相同，左连接(LEFT JOIN)与左外部连接(LEFT OUTER JOIN)相同。在关系数据库中，JOIN -type也可以是完整的外部连接(FULL OUTER)或交叉连接(CROSS,)，尽管在N1QL中目前不支持完整的外部连接(FULL OUTER JOIN)和交叉连接(CROSS JOIN)。

ANSI JOIN支持的详细信息

我们将使用示例向您展示使用ANSI连接(ANSI JOIN)语法运行查询的新方法，以及如何将N1QL中的现有连接查询从查找连接(lookup join)或索引连接(index join)语法转换为新的ANSI连接(ANSI JOIN)语法。

应该注意的是,查找连接(lookup join)和索引连接(index join)将继续支持N1QL向后兼容性,但是你不能混合在一起使用查找连接(lookup join)或索引连接(index join)或新ANSI连接(ANSI JOIN)查询语法,从而鼓励大家都迁移到新ANSI JOIN语法。

接下来，安装travel-sample样品桶。

示例1:具有任意连接条件的ANSI连接

ANSI join的join条件(ON-clause)可以是任何表达式，包括被连接的文档的任何字段。

例如:

Required index:

CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport)
WHERE type = “route”;

Optional index:

CREATE INDEX airport_city_country ON `travel-sample`(city, country) WHERE type =
“airport”;

Query:

SELECT DISTINCT route.destinationairport
FROM `travel-sample` airport JOIN `travel-sample` route
ON airport.faa = route.sourceairport
AND route.type = "route"
WHERE airport.type = "airport"
AND airport.city = "San Francisco"
AND airport.country = "United States";

在这个查询中，我们将airport文档中的字段(“faa”)与route文档中的字段(“sourceairport”)连接在一起(请参阅join的ON子句)。对于N1QL中的查找连接(lookup join)或索引连接(index join)，这种连接是不可能的，因为两者要求在文档键上连接。

ANSI JOIN需要在右边的keyspace上有一个适当的索引(“Required index”在上面)。您还可以创建其他索引(例如上面的“Optional index 可选索引”)来加速查询。如果没有可选索引，将使用主扫描，查询仍然有效，但是如果没有Required index，查询将无法工作，并将返回一个错误。

通过explain查看：

"plan": {
    "#operator": "Sequence",
    "~children": [{
            "#operator": "IndexScan3",
            "as": "airport",
            "index": "airport_city_country",
            "index_id": "8e782fd1b124eec3",
            "index_projection": {
                "primary_key": true
            },
            "keyspace": "travel-sample",
            "namespace": "default",
            "spans": [{
                "exact": true,
                "range": [{
                        "high": "\"San Francisco\"",
                        "inclusion": 3,
                        "low": "\"San Francisco\""
                    },
                    {
                        "high": "\"United States\"",
                        "inclusion": 3,
                        "low": "\"United States\""
                    }
                ]
            }],
            "using": "gsi"
        },
        {
            "#operator": "Fetch",
            "as": "airport",
            "keyspace": "travel-sample",
            "namespace": "default"
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [{
                        "#operator": "NestedLoopJoin",
                        "alias": "route",
                        "on_clause": "(((`airport`.`faa`) = cover ((`route`.`sourceairport`))) and
                            (cover((`route`.
                                    `type`)) = \"route\"))",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [{
                                        "#operator": "IndexScan3",
                                        "as": "route",
                                        "covers": [
                                            "cover ((`route`.`sourceairport`))",
                                            "cover ((`route`.`destinationairport`))",
                                            "cover ((meta(`route`).`id`))"
                                        ],
                                        "filter_covers": {
                                            "cover ((`route`.`type`))": "route"
                                        },
                                        "index": "route_airports",
                                        "index_id": "f1f4b9fbe85e45fd",
                                        "keyspace": "travel-sample",
                                        "namespace": "default",
                                        "nested_loop": true,
                                        "spans": [{
                                            "exact": true,
                                            "range": [{
                                                "high": "(`airport`.`faa`)",
                                                "inclusion": 3,
                                                "low": "(`airport`.`faa`)"
                                            }]
                                        }],
                                        "using": "gsi"
                                    }]
                                }
                            },
                        {
                            "#operator": "Filter",
                            "condition": "((((`airport`.`type`) = \"airport\") and ((`airport`.`city`) = \"San Francisco\")) and ((`airport`.`country`) = \"United States\"))"
                        },
                        {
                            "#operator": "InitialProject",
                            "distinct": true,
                            "result_terms": [{
                                "expr": "cover ((`route`.`destinationairport`))"
                            }]
                        },
                        {
                            "#operator": "Distinct"
                        },
                        {
                            "#operator": "FinalProject"
                        }
                    ]
                }
            },
            {
                "#operator": "Distinct"
            }
        ]
    }

您将看到NestedLoop Join操作符用于执行连接，在其下面使用IndexScan3操作符来访问右边的keyspace “route”。

索引扫描的跨度如下:

"spans": [{
    "exact": true,
    "range": [{
        "high": "(`airport`.`faa`)",
        "inclusion": 3,
        "low": "(`airport`.`faa`)"
    }]
}]

对右侧密钥空间(“route”)的索引扫描使用来自左侧keyspace (“airport”)的字段(“faa”)作为搜key。

对于来自外部keyspace“airport”的每个文档，NestedLoopJoin操作符在内部keyspace“route”上执行索引扫描，以查找匹配的文档，并生成连接结果。join是在nestedloop时尚中执行的，其中外部循环从外部的keyspace生成文档，并且一个嵌套的内部循环搜索用于匹配当前外部文档的内部文档。

通过单击explain按钮，然后单击Plan按钮，可以在Query Workbench中以图形方式查看explain信息:

Visual Explain(可视化分析) for ANSI JOIN

在本例中，右边键空间（keyspace）上的索引扫描是覆盖索引扫描。如果没有覆盖索引扫描，则fetch操作符将跟随索引扫描操作符来获取文档。

应该注意的是，nested-loop连接需要在ANSI连接的右侧keyspace上有一个合适的辅助索引。主索引没有考虑到这个目的。如果找不到合适的辅助，查询将返回一个错误。

此外，您可能已经注意到了过滤route.type = “route”也出现在ON-clause中。on -子句与WHERE子句不同，on -子句作为连接的一部分进行计算，而WHERE子句在所有连接完成之后进行计算。这种区别很重要，特别是对于外部连接(outer joins)。因此，建议您在on -子句中加入过滤器，并添加任何连接过滤器。

示例2:具有多个连接条件的ANSI连接

虽然查找连接(lookup join)和索引连接(index join)只在一个连接条件(文档键相等)上进行连接，但是ANSI连接的on -子句可以包含多个连接条件。

Required index:

CREATE INDEX landmark_city_country ON `travel-sample`(city, country) WHERE type =
“landmark”; Optional index:
CREATE INDEX hotel_title ON `travel-sample`(title) WHERE type = “hotel”;

Query:

SELECT hotel.name hotel_name, landmark.name landmark_name, landmark.activity
FROM `travel-sample` hotel JOIN `travel-sample` landmark
ON hotel.city = landmark.city AND hotel.country = landmark.country AND landmark.
type = “landmark”
WHERE hotel.type = “hotel” AND hotel.title like “Yosemite%” AND array_length(hotel.
public_likes) > 5;

看一下解释，索引跨越了右侧键空间(“landmark”)的索引(“landmark_city_country”):

"spans": [{
    "exact": true,
    "range": [{
            "high": "(`hotel`.`city`)",
            "inclusion": 3,
            "low": "(`hotel`.`city`)"
        },
        {
            "high": "(`hotel`.`country`)",
            "inclusion": 3,
            "low": "(`hotel`.`country`)"
        }
    ]
}]

因此，多个连接谓词可能会生成多个索引搜索键，以用于nested-loop连接的内侧索引扫描。

示例3:具有复杂连接表达式的ANSI连接

on子句中的连接条件可以是复杂的连接表达式。例如，“route”文档中的“airlineid”字段对应于“airline”文档的文档键，但也可以通过将“airline_”与“airline”文档的“id”字段连接起来来构造。

Required index:

CREATE INDEX route_airlineid ON `travel-sample`(airlineid) WHERE type = “route”;

Optional index:

CREATE INDEX airline_name ON `travel-sample`(name) WHERE type = “airline”;

Query:

SELECT count(*)
FROM `travel-sample` airline JOIN `travel-sample` route
ON route.airlineid = “airline_” || tostring(airline.id) AND route.type = “route”
WHERE airline.type = “airline” AND airline.name = “United Airlines”;

该解释包含右侧键空间keyspace(“route”)的以下索引范围:

"spans": [{
    "exact": true,
    "range": [{
        "high": "(\"airline_\" || to_string((`airline`.`id`)))",
        "inclusion": 3,
        "low": "(\"airline_\" || to_string((`airline`.`id`)))"
    }]
}]

该表达式将在运行时进行计算，以生成neste -loop连接内部端的索引扫描的搜索键。

例4:ANSI与IN子句连接。

联接条件不需要是相等谓词。插入子句可以用作连接条件。

Required index:

CREATE INDEX airport_faa_name ON `travel-sample`(faa, airportname) WHERE type =
“airport”;

Optional index:

CREATE INDEX route_airline_distance ON `travel-sample`(airline, distance) WHERE type =
“route”;

Query:

SELECT DISTINCT airport.airportname
FROM `travel-sample` route JOIN `travel-sample` airport
ON airport.faa IN [ route.sourceairport, route.destinationairport ] AND airport.
type = “airport”
WHERE route.type = “route” AND route.airline = “F9” AND route.distance > 3000;

explain包含右侧keyspace(“airport”)的以下索引跨度:

"spans": [{
        "range": [{
            "high": "(`route`.`sourceairport`)",
            "inclusion": 3,
            "low": "(`route`.`sourceairport`)"
        }]
    },
    {
        "range": [{
            "high": "(`route`.`destinationairport`)",
            "inclusion": 3,
            "low": "(`route`.`destinationairport`)"
        }]
    }
]

例5:ANSI JOIN 使用 OR子句

与in子句类似，ANSI连接的连接条件也可以包含or子句。

or子句的不同单o条件可以潜在地引用右边键空间（keyspace）的不同字段，只要适当的索引存在。

Required index (route_airports index same as example 1):

CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport) WHERE
type = “route”;
CREATE INDEX route_airports2 ON `travel-sample`(destinationairport, sourceairport)
WHERE type = “route”;

Optional index (same as example 1):

CREATE INDEX airport_city_country ON `travel-sample`(city, country) WHERE type =
“airport”;

Query:

SELECT count(*)
FROM `travel-sample` airport JOIN `travel-sample` route
ON (route.sourceairport = airport.faa OR route.destinationairport = airport.faa)
AND route.type = “route”
WHERE airport.type = “airport” AND airport.city = “Denver” AND airport.country =
“United States”;

解释显示在NestedLoopJoin中使用一个UnionScan来处理or -子句:

"#operator": "UnionScan",
"scans": [{
        "#operator": "IndexScan3",
        "as": "route",
        "index": "route_airports",
        "index_id": "f1f4b9fbe85e45fd",
        "index_projection": {
            "primary_key": true
        },
        "keyspace": "travel-sample",
        "namespace": "default",
        "nested_loop": true,
        "spans": [{
            "exact": true,
            "range": [{
                "high": "(`airport`.`faa`)",
                "inclusion": 3,
                "low": "(`airport`.`faa`)"
            }]
        }],
        "using": "gsi"
    },
    {
        "#operator": "IndexScan3",
        "as": "route",
        "index": "route_airports2",
        "index_id": "cdc9dca18c973bd3",
        "index_projection": {
            "primary_key": true
        },
        "keyspace": "travel-sample",
        "namespace": "default",
        "nested_loop": true,
        "spans": [{
            "exact": true,
            "range": [{
                "high": "(`airport`.`faa`)",
                "inclusion": 3,
                "low": "(`airport`.`faa`)"
                32
            }]
        }],
        "using": "gsi"
    }
]

示例6:ANSI连接使用索引提示

对于查找连接（lookup join）和索引连接（index join），提示只能在连接左侧的键空间中指定。对于ANSI连接，也可以在右边的keyspace上指定提示。

使用与示例1相同的查询(添加使用索引提示):

SELECT DISTINCT route.destinationairport
FROM `travel-sample` airport JOIN `travel-sample` route USE INDEX(route_airports)
ON airport.faa = route.sourceairport AND route.type = “route”
WHERE airport.type = “airport” AND airport.city = “San Francisco” AND airport.country =
“United States”;

使用索引提示限制计划人员执行联接时需要考虑的索引数量。

提示也可以在ANSI JOIN的左侧键空间中指定。

SELECT DISTINCT route.destinationairport
FROM `travel-sample` airport USE INDEX(airport_city_country)
JOIN `travel-sample` route USE INDEX(route_airports)
ON airport.faa = route.sourceairport AND route.type = “route”
WHERE airport.type = “airport” AND airport.city = “San Francisco” AND airport.country =
“United States”;

示例7:ANSI 左外部连接（ANSI LEFT OUTER JOIN）。

到目前为止，我们一直在研究内连接（inner joins.）。

您还可以通过在JOIN规范中在JOIN关键字前面包含LEFT或LEFT JOIN关键字来执行左外连接.

Required index (same as example 1):

CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport)
WHERE type = “route”;

Optional index (same as example 1):

CREATE INDEX airport_city_country ON `travel-sample`(city, country) WHERE type =
“airport”;

Query:

SELECT airport.airportname, route.airlineid
FROM `travel-sample` airport LEFT JOIN `travel-sample` route
ON airport.faa = route.sourceairport AND route.type = “route”
WHERE airport.type = “airport” AND airport.city = “Denver” AND airport.country =
“United States”;

该查询的结果集包含所有已连接的结果，以及任何不根据左外部连接的语义与右端(“route”)文档连接的左侧(“airport”)文档。因此，您将找到包含机场的结果。airportname但不是route。airlineid(缺失)。您还可以通过在右侧keyspace(“route”)上添加IS MISSING谓词(“route”)来选择不与右侧(“route”)文档连接的左侧(“airport”)文档:

SELECT airport.airportname, route.airlineid
FROM `travel-sample` airport LEFT JOIN `travel-sample` route
ON airport.faa = route.sourceairport AND route.type = “route”
WHERE airport.type = “airport” AND airport.city = “Denver” AND airport.country =
“United States”
AND route.airlineid IS MISSING;

例8:ANSI右外连接(ANSI RIGHT OUTER JOIN)。

ANSI的右外连接类似于ANSI左外连接，但如果没有连接，则保留右端文档。

我们可以通过切换左侧和右侧的keyspace来修改示例7中的查询，并使用RIGHT关键字替换左关键字:

SELECT airport.airportname, route.airlineid
FROM `travel-sample` route RIGHT JOIN `travel-sample` airport
ON airport.faa = route.sourceairport AND route.type = “route”
WHERE airport.type = “airport” AND airport.city = “Denver” AND airport.country =
“United States”;

注意，虽然我们在join规范中切换了airport和route，但是route上的筛选器(现在是左侧键空间)仍然会出现在join的on -子句中，因为route仍然在这个外部连接的从属端。

右外连接在内部转换为左外连接。

如果查询包含多个连接，则右外连接只能是指定的第一个连接。因为N1QL只支持线性连接，即。每个连接的右侧都必须是一个单独的键空间，如果一个右外连接不是第一个指定的连接，那么在转换为左外连接后，连接的右边现在包含多个键空间，这是不支持的。如果在第一个连接之外的任何位置指定RIGHT OUTER JOIN，将返回语法错误。

示例9:使用散列连接(Hash Join)的ANSI连接

N1QL支持ANSI连接的两个连接方法。ANSI连接的默认连接方法是嵌套循环连接(nested-loop)。另一种选择是散列连接(Hash Join)。散列连接使用散列表来匹配连接两边的文档。散列连接有一个构建端和一个探针端，其中来自构建端的每个文档都根据构建端等连接表达式的值插入到一个散列表中;随后，来自探针端的每个文档都将基于探针端的等连接表达式的值从哈希表中查找。如果找到匹配，则执行连接操作。

与嵌套循环连接(neste -loop)相比，散列连接在连接较大时更有效，例如，当连接左侧有几万或更多的文档时(在应用过滤器之后)。如果使用neste -loop连接，那么对于来自左侧的每个文档，需要对右侧索引执行索引扫描。随着左侧文档数量的增加，嵌套循环连接的效率降低。

对于散列连接(Hash Join)，连接的较小部分应该用于构建散列表，而连接的较大部分应该用于探测散列表。应该注意的是，散列连接确实比嵌套循环连接(neste -loop)需要更多的内存，因为需要内存中的散列表。所需的内存量与来自构建端文档的数量以及每个文档的平均大小成正比。

仅在企业版中支持散列连接(Hash Join)。要使用散列连接，必须在ANSI连接的右边键空间中指定使用散列提示。

使用与示例1相同的查询:

SELECT DISTINCT route.destinationairport
FROM `travel-sample` airport JOIN `travel-sample` route USE HASH(build)
ON airport.faa = route.sourceairport AND route.type = “route”
WHERE airport.type = “airport” AND airport.city = “San Jose” AND airport.country =
“United States”;

使用HASH(build)提示指示N1QL计划程序执行指定的ANSI连接的散列连接(Hash Join)，而右侧的keyspace(“route”)则用于散列连接的构建端。

查看explain，有一个HashJoin操作符:

{
    "#operator": "HashJoin",
    "build_aliases": [
        "route"
    ],
    "build_exprs": [
        "cover ((`route`.`sourceairport`))"
    ],
    "on_clause": "(((`airport`.`faa`) = cover ((`route`.`sourceairport`))) and (cover
        ((`route`.
            `type`)) = \"route\"))",
    "probe_exprs": [
        "(`airport`.`faa`)"
    ],
    "~child": {
        "#operator": "Sequence",
        "~children": [{
            "#operator": "IndexScan3",
            "as": "route",
            "covers": [
                "cover ((`route`.`sourceairport`))",
                "cover ((`route`.`destinationairport`))",
                "cover ((meta(`route`).`id`))"
            ],
            "filter_covers": {
                "cover ((`route`.`type`))": "route"
            },
            "index": "route_airports",
            "index_id": "f1f4b9fbe85e45fd",
            "keyspace": "travel-sample",
            "namespace": "default",
            "spans": [{
                "range": [{
                    "inclusion": 0,
                    "low": "null"
                }]
            }],
            "using": "gsi"
        }]
    }
}

HashJoin操作符的子操作符(“~child”)始终是散列连接的构建端。对于这个查询，它是右侧keyspace“route”.”的索引扫描。

注意，对于访问“route”文档，我们不能再使用来自左侧键空间keyspace(“airport”)的信息作为索引搜索键(请参阅上面解释部分中的“span”信息)。

与nested-loop连接不同，“route”上的索引扫描不再绑定到左侧的单个文档，因此“airport”文档中的任何值都不能用作“route”上的索引扫描的搜索键。

上面的查询中使用的使用散列(build)提示指示计划者使用右边的键空间作为散列连接的构建部分。您还可以指定使用散列(probe)提示来指示计划器使用右边的keyspace作为散列连接的探测端。

SELECT DISTINCT route.destinationairport
FROM `travel-sample` airport JOIN `travel-sample` route USE HASH(probe)
ON airport.faa = route.sourceairport AND route.type = “route”
WHERE airport.type = “airport” AND airport.city = “San Jose” AND airport.country =
“United States”;

查看explain，您将找到HashJoin操作符:

{
    "#operator": "HashJoin",
    "build_aliases": [
        "airport"
    ],
    "build_exprs": [
        "(`airport`.`faa`)"
    ],
    "on_clause": "(((`airport`.`faa`) = cover ((`route`.`sourceairport`))) and (cover
        ((`route`.
            `type`)) = \"route\"))",
    "probe_exprs": [
        "cover ((`route`.`sourceairport`))"
    ],
    "~child": {
        "#operator": "Sequence",
        "~children": [{
                "#operator": "IntersectScan",
                "scans": [{
                        "#operator": "IndexScan3",
                        "as": "airport",
                        "index": "airport_city_country",
                        "index_id": "8e782fd1b124eec3",
                        "index_projection": {
                            37 "primary_key": true
                        },
                        "keyspace": "travel-sample",
                        "namespace": "default",
                        "spans": [{
                            "exact": true,
                            "range": [{
                                    "high": "\"San Jose\"",
                                    "inclusion": 3,
                                    "low": "\"San Jose\""
                                },
                                {
                                    "high": "\"United States\"",
                                    "inclusion": 3,
                                    "low": "\"United States\""
                                }
                            ]
                        }],
                        "using": "gsi"
                    },
                    {
                        "#operator": "IndexScan3",
                        "as": "airport",
                        "index": "airport_faa",
                        "index_id": "c302afbf811470f5",
                        "index_projection": {
                            "primary_key": true
                        },
                        "keyspace": "travel-sample",
                        "namespace": "default",
                        "spans": [{
                            "exact": true,
                            "range": [{
                                "inclusion": 0,
                                "low": "null"
                            }]
                        }],
                        "using": "gsi"
                    }
                ]
            },
            {
                38 "#operator": "Fetch",
                "as": "airport",
                "keyspace": "travel-sample",
                "namespace": "default"
            }
        ]
    }
}

HashJoin的子操作符(“~child”)是在ANSI连接的左侧键空间“airport”上的一个交叉索引扫描，后面跟着一个fetch操作符。

使用散列提示只能在ANSI连接的右边键空间中指定。因此，根据您希望右边的键空间是build端还是散列连接的probe端，应该在右边的键空间中指定使用HASH(build)或使用HASH(probe)提示。

只有在指定使用HASH(build)或使用HASH(probe)提示时才考虑散列连接。散列连接需要相等连接谓词才能工作。嵌套循环（Nested-loop）连接需要右边键空间上适当的辅助索引，而散列连接则不需要(主索引扫描是散列连接的一个选项)。然而，与嵌套循环连接相比，散列连接确实需要更多的内存，因为散列连接工作需要内存中的散列表。此外，散列连接被认为是一种“阻塞”操作，这意味着查询引擎必须在生成第一个连接结果之前完成散列表的构建，因此对于只需要前几个结果的查询(例如使用一个LIMIT子句)，散列连接可能不是最佳匹配。

如果指定了USE HASH提示，但无法成功生成散列连接(例如，缺少相等连接谓词)，则将考虑使用nested-loop连接。

示例10:带有多个提示的ANSI连接

现在，您可以为ANSI连接右侧的keyspace指定多个提示。例如，USE HASH提示可以与USE INDEX提示一起使用。

SELECT DISTINCT route.destinationairport
FROM `travel-sample` airport JOIN `travel-sample` route USE HASH(probe)
INDEX(route_airports)
ON airport.faa = route.sourceairport AND route.type = “route”
WHERE airport.type = “airport” AND airport.city = “San Jose” AND airport.country =
“United States”;

注意，当多个提示一起使用时，您只需指定“USE”关键字一次，如上面的示例所示。

USE HASH提示还可以与USE KEYS提示相结合。

示例11:ANSI连接多个连接

ANSI连接可以链接在一起。例如:

Required indexes (route_airports index same as example 1):

CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport)
WHERE type = “route”;
CREATE INDEX airline_iata ON `travel-sample`(iata) WHERE type = “airline”;
Optional index (same as example 1):
CREATE INDEX airport_city_country ON `travel-sample`(city, country) WHERE type =
“airport”;

Query:

SELECT DISTINCT airline.name
FROM `travel-sample` airport INNER JOIN `travel-sample` route
ON airport.faa = route.sourceairport AND route.type = “route”
INNER JOIN `travel-sample` airline
ON route.airline = airline.iata AND airline.type = “airline”
WHERE airport.type = “airport” AND airport.city = “San Jose”
AND airport.country = “United States”;

由于查询中没有指定USE HASH提示，explain应该显示两个NestedLoopJoin操作符。

通过向ANSI连接链中的任何连接添加USE HASH提示，可以将散列连接与nested-loop连接混合。

SELECT DISTINCT airline.name
FROM `travel-sample` airport INNER JOIN `travel-sample` route
ON airport.faa = route.sourceairport AND route.type = “route”
INNER JOIN `travel-sample` airline USE HASH(build)
ON route.airline = airline.iata AND airline.type = “airline”
WHERE airport.type = “airport” AND airport.city = “San Jose”
AND airport.country = “United States”;

或者

SELECT DISTINCT airline.name
FROM `travel-sample` airport INNER JOIN `travel-sample` route USE HASH(probe)
ON airport.faa = route.sourceairport AND route.type = “route”
INNER JOIN `travel-sample` airline
ON route.airline = airline.iata AND airline.type = “airline”
WHERE airport.type = “airport” AND airport.city = “San Jose”
AND airport.country = “United States”;

最后一个查询的可视化解释如下:

如前所述，N1QL只支持线性连接，即。每个连接的右边都必须是一个键空间。

示例12:包含右边数组的ANSI连接

虽然ANSI连接来自SQL标准，但是由于Couchbase N1QL处理JSON文档，而数组是JSON的一个重要方面，所以我们也将ANSI连接支持扩展到数组。

例如，在数组处理中，请创建一个bucket“default”并插入以下文档:

INSERT INTO default (KEY,VALUE) VALUES("test11_ansijoin", {"c11": 1, "c12": 10, "a11":
[ 1, 2, 3, 4 ], "type": "left"}),
VALUES("test12_ansijoin", {"c11": 2, "c12": 20, "a11": [ 3, 3, 5, 10
], "type": "left"}),
VALUES("test13_ansijoin", {"c11": 3, "c12": 30, "a11": [ 3, 4, 20, 40
41
], "type": "left"}),
VALUES("test14_ansijoin", {"c11": 4, "c12": 40, "a11": [ 30, 30, 30 ],
"type": "left"});
INSERT INTO default (KEY,VALUE) VALUES("test21_ansijoin", {"c21": 1, "c22": 10, "a21":
[ 1, 10, 20], "a22": [ 1, 2, 3, 4 ], "type": "right"}),
VALUES("test22_ansijoin", {"c21": 2, "c22": 20, "a21": [ 2, 3, 30],
"a22": [ 3, 5, 10, 3 ], "type": "right"}),
VALUES("test23_ansijoin", {"c21": 2, "c22": 21, "a21": [ 2, 20, 30],
"a22": [ 3, 3, 5, 10 ], "type": "right"}),
VALUES("test24_ansijoin", {"c21": 3, "c22": 30, "a21": [ 3, 10, 30],
"a22": [ 3, 4, 20, 40 ], "type": "right"}),
VALUES("test25_ansijoin", {"c21": 3, "c22": 31, "a21": [ 3, 20, 40],
"a22": [ 4, 3, 40, 20 ], "type": "right"}),
VALUES("test26_ansijoin", {"c21": 3, "c22": 32, "a21": [ 4, 14, 24],
"a22": [ 40, 20, 4, 3 ], "type": "right"}),
VALUES("test27_ansijoin", {"c21": 5, "c22": 50, "a21": [ 5, 15, 25],
"a22": [ 1, 2, 3, 4 ], "type": "right"}),
VALUES("test28_ansijoin", {"c21": 6, "c22": 60, "a21": [ 6, 16, 26],
"a22": [ 3, 3, 5, 10 ], "type": "right"}),
VALUES("test29_ansijoin", {"c21": 7, "c22": 70, "a21": [ 7, 17, 27],
"a22": [ 30, 30, 30 ], "type": "right"}),
VALUES("test30_ansijoin", {"c21": 8, "c22": 80, "a21": [ 8, 18, 28],
"a22": [ 30, 30, 30 ], "type": "right"});

然后创建以下索引:

CREATE INDEX default_ix_left on default(c11, DISTINCT a11) WHERE type = “left”;
CREATE INDEX efault_ix_right on default(c21, DISTINCT a21) WHERE type = “right”;

当join谓词涉及到ANSI join右边的一个数组时，您需要在右边的keyspace上创建一个数组索引。

Query:

SELECT b1.c11, b2.c21, b2.c22
FROM default b1 JOIN default b2
ON b2.c21 = b1.c11 AND ANY v IN b2.a21 SATISFIES v = b1.c12 END AND b2.type =
“right”
WHERE b1.type = “left”;

注意，join条件的一部分是ANY子句，它指定左侧字段b1.c12可以匹配右边数组b2.a21中的任何元素。要让这个连接正常工作，我们需要在b2.a21 上建立一个数组索引。例如上面创建的default_ix_right索引。

explain计划显示了一个NestedLoopJoin，子操作符是数组索引default_ix_right上的一个不同的扫描。

{
    "#operator": "NestedLoopJoin",
    "alias": "b2",
    "on_clause": "((((`b2`.`c21`) = (`b1`.`c11`)) and any `v` in (`b2`.`a21`) satisfies
        (`v` = (`b1`.
            `c12`)) end) and((`b2`.
        `type`) = \"right\"))",
    "~child": {
        "#operator": "Sequence",
        "~children": [{
                "#operator": "DistinctScan",
                "scan": {
                    "#operator": "IndexScan3",
                    "as": "b2",
                    "index": "default_ix_right",
                    "index_id": "ef4e7fa33f33dce",
                    "index_projection": {
                        "primary_key": true
                    },
                    "keyspace": "default",
                    "namespace": "default",
                    "nested_loop": true,
                    "spans": [{
                        "exact": true,
                        "range": [{
                                "high": "(`b1`.`c11`)",
                                "inclusion": 3,
                                "low": "(`b1`.`c11`)"
                            },
                            {
                                "high": "(`b1`.`c12`)",
                                "inclusion": 3,
                                "low": "(`b1`.`c12`)"
                            }
                        ]
                    }],
                    "using": "gsi"
                }
            },
            {
                "#operator": "Fetch",
                "as": "b2",
                "keyspace": "default",
                "namespace": "default",
                "nested_loop": true
                43
            }
        ]
    }
}

示例13:涉及左手边数组的ANSI连接

如果ANSI JOIN涉及左手边的数组，那么有两个选项可以执行连接。

Option 1: 使用 UNNEST

在执行联接之前，使用UNNEST子句将左边的数组压平。

SELECT b1.c11, b2.c21, b2.c22
FROM default b1 UNNEST b1.a11 AS ba1
JOIN default b2 ON ba1 = b2.c21 AND b2.type = “right”
WHERE b1.c11 = 2 AND b1.type = “left”;

在UNNEST之后，数组就变成了单独的字段，随后的连接就像一个“常规”的ANSI连接，它与两边的字段连接在一起。

Option 2: 使用 IN-clause

或者，使用in -从句作为连接条件。

SELECT b1.c11, b2.c21, b2.c22
FROM default b1 JOIN default b2
ON b2.c21 IN b1.a11 AND b2.type = “right”
WHERE b1.c11 = 2 AND b1.type = “left”;

当左侧关键字空间(“b1.a11”)上的数组的任何元素与右侧字段(“b2.c21”)匹配时，IN-clause将得到满足。

注意，这两个选项之间存在语义上的差异。当数组中有重复的文档时，UNNEST选项不关心重复的文档，会将左边的文档压缩到数组中元素的数量，从而产生重复的结果;如果数组中有重复的元素，IN-clause选项将不会产生重复的结果。此外，当执行左外连接(LEFT OUTER JOIN)时，可能会有不同数量的保存文档，因为数组的扁平化和UNNEST选项。因此，建议用户选择反映查询所需语义的选项。

示例14:ANSI连接包含了两边的数组

虽然不常见，但是当连接的两边都是数组时，可以执行ANSI连接。在这种情况下，您可以使用上述技术的组合。

使用数组索引来处理右边的数组，并使用UNNEST选项或IN-clause选项来处理左边的数组。

Option 1: use UNNEST clause

SELECT b1.c11, b2.c21, b2.c22
FROM default b1 UNNEST b1.a11 AS ba1
JOIN default b2 ON b2.c21 = b1.c11 AND ANY v IN b2.a21 SATISFIES v = ba1 END AND
b2.type = “right”
WHERE b1.type = “left”;

Option 2: use IN-clause

SELECT b1.c11, b2.c21, b2.c22
FROM default b1 JOIN default b2
ON b2.c21 = b1.c11 AND ANY v IN b2.a21 SATISFIES v IN b1.a11 END AND b2.type =
“right”
WHERE b1.type = “left”;

同样，这两个选项在语义上并不相同，可能会产生不同的结果。选择反映所需语义的选项。

示例15:查找连接迁移

为了向后兼容，N1QL将继续支持查找连接(lookup join)和索引连接(index join)，但是，不能将ANSI连接与查找连接(lookup join)或索引连接(index join)混合在同一个查询中。您可以将现有查询从使用查找连接(lookup join)和索引连接(index join)转换为ANSI连接语法。这个示例向您展示如何将查找连接转换为ANSI连接语法。

创建以下索引以加速查询(与示例1相同):

CREATE INDEX route_airports ON `travel-sample`(sourceairport, destinationairport) WHERE
type = “route”;

这是一个使用查找连接语法的查询(注意ON KEYS子句):

SELECT airline.name
FROM `travel-sample` route JOIN `travel-sample` airline
ON KEYS route.airlineid
WHERE route.type = “route” AND route.sourceairport = “SFO” AND route.destinationairport
= “JFK”;

在查找联接的左侧(“route”)需要为联接的右侧(“airline”)生成文档键，这是通过ON keys子句实现的。连接条件(从语法中隐含)是route.airlineid = meta(airline).id,因此可以使用ANSI连接语法指定相同的查询:

SELECT airline.name
FROM `travel-sample` route JOIN `travel-sample` airline
ON route.airlineid = meta(airline).id
WHERE route.type = “route” AND route.sourceairport = “SFO” AND route.destinationairport
= “JFK”;

在本例中，ON KEYS子句包含一个文档键。ON KEYS子句可能包含一个文档key数组，在这种情况下，转换后的子句将以一个in子句的形式代替一个相等的子句。

让我们假设每个route文档都有一个针对reporter的文档key数组，然后是原始的ON keys子句:

ON KEYS route.airlineids

可以被转换成·

ON meta(airline).id IN route.airlineids

示例16:索引连接迁移

这个示例向您展示如何将索引连接(index join)转换为ANSI连接语法。所需索引(与示例3相同):

CREATE INDEX route_airlineid ON `travel-sample`(airlineid) WHERE type = “route”;
Optional index (same as example 3):
CREATE INDEX airline_name ON `travel-sample`(name) WHERE type = “airline”;
Query using index join syntax (note the ON KEY … FOR … clause):
SELECT count(*)
FROM `travel-sample` airline JOIN `travel-sample` route
ON KEY route.airlineid FOR airline
WHERE airline.type = “airline” AND route.type = “route” AND airline.name = “United
Airlines”;