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

Introducci\u00f3n<\/span><\/h2>\n

El soporte de N1QL para ANSI JOIN se introdujo por primera vez en la versi\u00f3n 5.5. Actualmente existe una restricci\u00f3n en ANSI JOIN (versiones 5.5 y 6.0) en el sentido de que el lado derecho de un ANSI JOIN debe ser un espacio de claves. Esta restricci\u00f3n se suprimir\u00e1 en la versi\u00f3n 6.5.<\/span><\/p>\n

El soporte N1QL para la sentencia MERGE utiliza actualmente la cl\u00e1usula ON KEYS, similar a lookup join. La versi\u00f3n 6.5 soportar\u00e1 la sintaxis MERGE ANSI.<\/span><\/p>\n

 <\/p>\n

Mejoras de ANSI JOIN<\/span><\/h2>\n

En la versi\u00f3n 6.5 de Couchbase Server, N1QL a\u00f1ade soporte para expresi\u00f3n y subconsulta como lado derecho de un ANSI JOIN. Anteriormente en las versiones 5.5 y 6.0, el lado derecho de un ANSI JOIN deb\u00eda ser un espacio de claves. Se devuelve un error si el lado derecho de un ANSI JOIN no es un espacio de claves.<\/span><\/p>\n

La restricci\u00f3n para el espacio clave s\u00f3lo existe en las versiones 5.5 y 6.0, y s\u00f3lo se aplica al lado derecho de un ANSI JOIN. El lado izquierdo de un ANSI JOIN puede ser un espacio clave, una expresi\u00f3n, una subconsulta u otra operaci\u00f3n join\/nest\/unnest, siempre que ANSI JOIN no se mezcle con una operaci\u00f3n join\/nest que no sea ANSI.<\/span><\/p>\n

Expresi\u00f3n como lado derecho de un ANSI JOIN<\/span><\/h3>\n

Ahora se puede utilizar una expresi\u00f3n en el lado derecho de ANSI JOIN. Por ejemplo:<\/span><\/p>\n

SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN\r\n  [ {\"destinationairport\": \"KEF\", \"sourceairport\": \"SFO\", \"type\": \"route\"},\r\n    {\"destinationairport\": \"KEF\", \"sourceairport\": \"LHR\", \"type\": \"route\"} ] AS route\r\n  ON airport.faa = route.sourceairport AND route.type = \"route\"\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Francisco\";<\/pre>\n
En este ejemplo, se especifica una matriz expl\u00edcita de objetos como una expresi\u00f3n en el lado derecho de un ANSI JOIN. Tenga en cuenta que cuando se utiliza una expresi\u00f3n en el lado derecho de un ANSI JOIN, la expresi\u00f3n debe tener un alias (\"ruta\" en el ejemplo anterior, aunque la palabra clave AS es opcional).\u00a0<\/span><\/p>\n
Cuando se utiliza una expresi\u00f3n en el lado derecho de un ANSI JOIN, se utiliza un operador ExpressionScan para iterar a trav\u00e9s del conjunto de resultados de la expresi\u00f3n en el plan explain:<\/span><\/p>\n
\"plan\": {\r\n    \"#operator\": \"Sequence\",\r\n    \"~children\": [\r\n        {\r\n            \"#operator\": \"IndexScan3\",\r\n            \"as\": \"airport\",\r\n            \"index\": \"ix_airport_city\",\r\n            \"index_id\": \"eee67e7e615a1b49\",\r\n            \"index_projection\": {\r\n                \"primary_key\": true\r\n            },\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\",\r\n            \"spans\": [\r\n                {\r\n                    \"exact\": true,\r\n                    \"range\": [\r\n                        {\r\n                            \"high\": \"\\\"San Francisco\\\"\",\r\n                            \"inclusion\": 3,\r\n                            \"low\": \"\\\"San Francisco\\\"\"\r\n                        }\r\n                    ]\r\n                }\r\n            ],\r\n            \"using\": \"gsi\"\r\n        },\r\n        {\r\n            \"#operator\": \"Fetch\",\r\n            \"as\": \"airport\",\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\"\r\n        },\r\n        {\r\n            \"#operator\": \"HashJoin\",\r\n            \"build_aliases\": [\r\n                \"route\"\r\n            ],\r\n            \"build_exprs\": [\r\n                \"(`route`.`sourceairport`)\"\r\n            ],\r\n            \"on_clause\": \"(((`airport`.`faa`) = (`route`.`sourceairport`)) and ((`route`.`type`) = \\\"route\\\"))\",\r\n            \"probe_exprs\": [\r\n                \"(`airport`.`faa`)\"\r\n            ],\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"ExpressionScan\",\r\n                        \"alias\": \"route\",\r\n                        \"expr\": \"[{\\\"destinationairport\\\": \\\"KEF\\\", \\\"sourceairport\\\": \\\"SFO\\\", \\\"type\\\": \\\"route\\\"}, {\\\"destinationairport\\\": \\\"KEF\\\", \\\"sourceairport\\\": \\\"LHR\\\", \\\"type\\\": \\\"route\\\"}]\",\r\n                        \"uncorrelated\": true\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Parallel\",\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"Filter\",\r\n                        \"condition\": \"(((`airport`.`type`) = \\\"airport\\\") and ((`airport`.`city`) = \\\"San Francisco\\\"))\"\r\n                    },\r\n                    {\r\n                        \"#operator\": \"InitialProject\",\r\n                        \"distinct\": true,\r\n                        \"result_terms\": [\r\n                            {\r\n\r\n                                \"expr\": \"(`route`.`destinationairport`)\"\r\n                            }\r\n                        ]\r\n                    },\r\n                    {\r\n                        \"#operator\": \"Distinct\"\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Distinct\"\r\n        }\r\n    ]\r\n},\r\n<\/pre>\n
 <\/p>\n
Subconsulta como lado derecho de un ANSI JOIN<\/span><\/h3>\n
Tambi\u00e9n se puede utilizar una subconsulta en el lado derecho de ANSI JOIN. Por ejemplo:<\/span><\/p>\n
SELECT DISTINCT route.destinationairport\r\nFROM `travel-sample` airport JOIN\r\n ( SELECT destinationairport, sourceairport\r\n   FROM `travel-sample` WHERE type = \"route\" ) AS route\r\n ON airport.faa = route.sourceairport\r\nWHERE airport.type = \"airport\" AND airport.city = \"San Francisco\";\r\n<\/pre>\n
En este ejemplo, se utiliza una subconsulta en el lado derecho de un ANSI JOIN. Al igual que una expresi\u00f3n, cuando se utiliza una subconsulta en el lado derecho de un ANSI JOIN, la subconsulta tambi\u00e9n debe tener un alias (\"ruta\" en el ejemplo anterior, aunque la palabra clave AS es opcional).<\/span><\/p>\n
Cuando se utiliza una subconsulta en el lado derecho de un ANSI JOIN, la explicaci\u00f3n muestra el plan de la propia subconsulta:<\/span><\/p>\n
\"plan\": {\r\n    \"#operator\": \"Sequence\",\r\n    \"~children\": [\r\n        {\r\n            \"#operator\": \"IndexScan3\",\r\n            \"as\": \"airport\",\r\n            \"index\": \"ix_airport_city\",\r\n            \"index_id\": \"eee67e7e615a1b49\",\r\n            \"index_projection\": {\r\n                \"primary_key\": true\r\n            },\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\",\r\n            \"spans\": [\r\n                {\r\n                    \"exact\": true,\r\n                    \"range\": [\r\n                        {\r\n                            \"high\": \"\\\"San Francisco\\\"\",\r\n                            \"inclusion\": 3,\r\n                            \"low\": \"\\\"San Francisco\\\"\"\r\n                        }\r\n                    ]\r\n                }\r\n            ],\r\n            \"using\": \"gsi\"\r\n        },\r\n        {\r\n            \"#operator\": \"Fetch\",\r\n            \"as\": \"airport\",\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\"\r\n        },\r\n        {\r\n            \"#operator\": \"HashJoin\",\r\n            \"build_aliases\": [\r\n                \"route\"\r\n            ],\r\n            \"build_exprs\": [\r\n                \"(`route`.`sourceairport`)\"\r\n            ],\r\n            \"on_clause\": \"((`airport`.`faa`) = (`route`.`sourceairport`))\",\r\n            \"probe_exprs\": [\r\n                \"(`airport`.`faa`)\"\r\n            ],\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"Sequence\",\r\n                        \"~children\": [\r\n                            {\r\n                                \"#operator\": \"IndexScan3\",\r\n                                \"index\": \"ix_type\",\r\n                                \"index_id\": \"d925e49b3a11ae3d\",\r\n                                \"index_projection\": {\r\n                                    \"primary_key\": true\r\n                                },\r\n                                \"keyspace\": \"travel-sample\",\r\n                                \"namespace\": \"default\",\r\n                                \"spans\": [\r\n                                    {\r\n                                        \"exact\": true,\r\n                                        \"range\": [\r\n                                            {\r\n                                                \"high\": \"\\\"route\\\"\",\r\n                                                \"inclusion\": 3,\r\n                                                \"low\": \"\\\"route\\\"\"\r\n                                            }\r\n                                        ]\r\n                                    }\r\n                                ],\r\n                                \"using\": \"gsi\"\r\n                            },\r\n\r\n                            {\r\n                                \"#operator\": \"Fetch\",\r\n                                \"keyspace\": \"travel-sample\",\r\n                                \"namespace\": \"default\"\r\n                            },\r\n                            {\r\n                                \"#operator\": \"Parallel\",\r\n                                \"~child\": {\r\n                                    \"#operator\": \"Sequence\",\r\n                                    \"~children\": [\r\n                                        {\r\n                                            \"#operator\": \"Filter\",\r\n                                            \"condition\": \"((`travel-sample`.`type`) = \\\"route\\\")\"\r\n                                        },\r\n                                        {\r\n                                            \"#operator\": \"InitialProject\",\r\n                                            \"result_terms\": [\r\n                                                {\r\n                                                    \"expr\": \"(`travel-sample`.`destinationairport`)\"\r\n                                                },\r\n                                                {\r\n                                                    \"expr\": \"(`travel-sample`.`sourceairport`)\"\r\n                                                }\r\n                                            ]\r\n\r\n                                        }\r\n                                    ]\r\n                                }\r\n                            }\r\n                        ]\r\n                    },\r\n                    {\r\n                        \"#operator\": \"Alias\",\r\n                        \"as\": \"route\"\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Parallel\",\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"Filter\",\r\n                        \"condition\": \"(((`airport`.`type`) = \\\"airport\\\") and ((`airport`.`city`) = \\\"San Francisco\\\"))\"\r\n                    },\r\n                    {\r\n                        \"#operator\": \"InitialProject\",\r\n\r\n                        \"distinct\": true,\r\n                        \"result_terms\": [\r\n                            {\r\n                                \"expr\": \"(`route`.`destinationairport`)\"\r\n                            }\r\n                        ]\r\n                    },\r\n                    {\r\n                        \"#operator\": \"Distinct\"\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Distinct\"\r\n        }\r\n    ]\r\n},\r\n<\/pre>\n
Cuando se utiliza una subconsulta como lado derecho de un ANSI JOIN, no puede ser una subconsulta correlacionada. Se devolver\u00e1 un error si se utiliza una subconsulta correlacionada en el lado derecho de un ANSI JOIN.<\/p>\n
Hash join utilizado cuando una expresi\u00f3n o una subconsulta est\u00e1 en el lado derecho de un ANSI JOIN<\/span><\/h3>\n
Cuando se utiliza un espacio de claves en el lado derecho de un ANSI JOIN, la uni\u00f3n en bucle anidado es el m\u00e9todo de uni\u00f3n por defecto, y se considera la uni\u00f3n hash cuando se especifica la sugerencia USE HASH en el espacio de claves del lado derecho. Adem\u00e1s, se requiere un \u00edndice secundario apropiado en el espacio clave cuando se utiliza la uni\u00f3n de bucle anidado. Por el contrario, cuando se utiliza una expresi\u00f3n o una subconsulta en el lado derecho de un ANSI JOIN, como no hay \u00edndice en una expresi\u00f3n o una subconsulta, es m\u00e1s eficiente utilizar una uni\u00f3n hash para realizar la uni\u00f3n. En este caso, una uni\u00f3n de bucle anidado es una uni\u00f3n cartesiana, ya que el conjunto de resultados de la expresi\u00f3n o subconsulta debe explorarse repetidamente para cada documento externo. Como resultado, cuando una expresi\u00f3n o subconsulta se encuentra en el lado derecho de un ANSI JOIN, la uni\u00f3n hash es el m\u00e9todo de uni\u00f3n por defecto, incluso sin especificar la sugerencia USE HASH. Si no se puede utilizar la uni\u00f3n hash, por ejemplo, si no hay un predicado de uni\u00f3n de igualdad, o cuando se utiliza la sugerencia USE NL, o cuando se utiliza la edici\u00f3n community (la uni\u00f3n hash s\u00f3lo est\u00e1 disponible en la edici\u00f3n enterprise), entonces se considera la uni\u00f3n de bucle anidado. Si se observan los archivos de explicaci\u00f3n anteriores para los casos de expresi\u00f3n y subconsulta como lado derecho de un ANSI JOIN, se puede encontrar un operador HashJoin utilizado en ambos archivos de explicaci\u00f3n.<\/span><\/p>\n
Cuando se utiliza un espacio clave en el lado derecho de un ANSI JOIN, se puede especificar una sugerencia USE INDEX o USE KEYS en el espacio clave. Cuando se utiliza una expresi\u00f3n o una subconsulta en el lado derecho de un ANSI JOIN, no se puede especificar una sugerencia USE INDEX o USE KEYS en la expresi\u00f3n o subconsulta, por razones obvias. La \u00fanica pista disponible en este caso es una pista join (USE HASH o USE NL).<\/span><\/p>\n
Compatibilidad con ANSI NEST<\/span><\/h3>\n
La misma mejora se aplica tambi\u00e9n a ANSI NEST, es decir, ahora puede utilizar una expresi\u00f3n o una subconsulta como lado derecho de un ANSI NEST.<\/span><\/p>\n
 <\/p>\n
FUSI\u00d3N ANSI<\/span><\/h2>\n
Una sentencia MERGE permite fusionar documentos del \"origen\" en el \"destino\", es decir, cuando se encuentra una coincidencia, se puede actualizar el documento destino; cuando no se encuentra ninguna coincidencia, se puede insertar un documento en el destino.<\/span><\/p>\n
El concepto de \"coincidencia\" viene determinado por la condici\u00f3n de coincidencia. En versiones anteriores de Couchbase N1QL s\u00f3lo soporta una \"coincidencia\" basada en la clave del documento, es decir, la fuente debe producir una clave de documento para el destino, para determinar si se encuentra una coincidencia (si el documento con esa clave de documento ya existe en el destino). Esto se consigue mediante la cl\u00e1usula ON KEYS de la sintaxis de la sentencia MERGE.<\/span><\/p>\n
La sentencia MERGE con la cl\u00e1usula ON KEYS se denomina look-up merge. Es similar a la sintaxis join anterior a la compatibilidad con ANSI JOIN, es decir, look-up join, que tambi\u00e9n requiere la uni\u00f3n en la clave del documento y utiliza la cl\u00e1usula ON KEYS. Por lo tanto, una extensi\u00f3n natural de la sentencia MERGE es utilizar la sintaxis ANSI MERGE, es decir, utilizar la cl\u00e1usula ON para especificar la condici\u00f3n de fusi\u00f3n, de forma que se pueda utilizar una expresi\u00f3n arbitraria para determinar si existe o no una coincidencia. ANSI MERGE est\u00e1 soportado en N1QL en la versi\u00f3n 6.5 del servidor Couchbase.<\/span><\/p>\n
A continuaci\u00f3n se muestra un ejemplo de sentencia MERGE ANSI:<\/span><\/p>\n
MERGE INTO `travel-sample` AS route USING `travel-sample` AS airport \r\n  ON route.sourceairport = airport.faa AND airport.type = \"airport\"\r\n   AND route.type = \"route\"\r\nWHEN MATCHED THEN UPDATE SET route.old_equipment = route.equipment,\r\n                             route.equipment = \"797\", route.updated = true\r\n     WHERE route.airline = \"BA\" AND airport.country = \"France\"\r\n       AND CONTAINS(route.equipment, \"319\");\r\n<\/pre>\n
En este ejemplo, la condici\u00f3n de fusi\u00f3n se especifica mediante una cl\u00e1usula ON. Esto es muy similar a la cl\u00e1usula ON de un ANSI JOIN. De hecho, internamente la sentencia ANSI MERGE utiliza ANSI JOIN con la misma cl\u00e1usula ON para determinar si existe una coincidencia para cualquier documento fuente.<\/span><\/p>\n
La explicaci\u00f3n de la declaraci\u00f3n de fusi\u00f3n anterior:<\/span><\/p>\n
\"plan\": {\r\n    \"#operator\": \"Sequence\",\r\n    \"~children\": [\r\n        {\r\n            \"#operator\": \"IndexScan3\",\r\n            \"as\": \"airport\",\r\n            \"index\": \"ix_type\",\r\n            \"index_id\": \"d925e49b3a11ae3d\",\r\n            \"index_projection\": {\r\n                \"primary_key\": true\r\n            },\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\",\r\n            \"spans\": [\r\n                {\r\n                    \"exact\": true,\r\n                    \"range\": [\r\n                        {\r\n                            \"high\": \"\\\"airport\\\"\",\r\n                            \"inclusion\": 3,\r\n                            \"low\": \"\\\"airport\\\"\"\r\n                        }\r\n                    ]\r\n                }\r\n            ],\r\n            \"using\": \"gsi\"\r\n        },\r\n        {\r\n            \"#operator\": \"Fetch\",\r\n            \"as\": \"airport\",\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\"\r\n        },\r\n        {\r\n            \"#operator\": \"Parallel\",\r\n            \"~child\": {\r\n                \"#operator\": \"Sequence\",\r\n                \"~children\": [\r\n                    {\r\n                        \"#operator\": \"NestedLoopJoin\",\r\n                        \"alias\": \"route\",\r\n                        \"on_clause\": \"((((`route`.`sourceairport`) = (`airport`.`faa`)) and ((`airport`.`type`) = \\\"airport\\\")) and ((`route`.`type`) = \\\"route\\\"))\",\r\n                        \"~child\": {\r\n                            \"#operator\": \"Sequence\",\r\n                            \"~children\": [\r\n                                {\r\n                                    \"#operator\": \"IndexScan3\",\r\n                                    \"as\": \"route\",\r\n                                    \"index\": \"ix_type\",\r\n                                    \"index_id\": \"d925e49b3a11ae3d\",\r\n                                    \"index_projection\": {\r\n                                        \"primary_key\": true\r\n                                    },\r\n                                    \"keyspace\": \"travel-sample\",\r\n                                    \"namespace\": \"default\",\r\n                                    \"nested_loop\": true,\r\n                                    \"spans\": [\r\n                                        {\r\n                                            \"exact\": true,\r\n                                            \"range\": [\r\n                                                {\r\n                                                    \"high\": \"\\\"route\\\"\",\r\n                                                    \"inclusion\": 3,\r\n                                                    \"low\": \"\\\"route\\\"\"\r\n                                                }\r\n                                            ]\r\n                                        }\r\n                                    ],\r\n                                    \"using\": \"gsi\"\r\n                                },\r\n                                {\r\n                                    \"#operator\": \"Fetch\",\r\n                                    \"as\": \"route\",\r\n                                    \"keyspace\": \"travel-sample\",\r\n                                    \"namespace\": \"default\",\r\n                                    \"nested_loop\": true\r\n                                }\r\n                            ]\r\n                        }\r\n                    },\r\n                    {\r\n                        \"#operator\": \"Merge\",\r\n                        \"as\": \"route\",\r\n                        \"keyspace\": \"travel-sample\",\r\n                        \"namespace\": \"default\",\r\n                        \"update\": {\r\n                            \"#operator\": \"Sequence\",\r\n                            \"~children\": [\r\n                                {\r\n                                    \"#operator\": \"Filter\",\r\n                                    \"condition\": \"((((`route`.`airline`) = \\\"BA\\\") and ((`airport`.`country`) = \\\"France\\\")) and contains((`route`.`equipment`), \\\"319\\\"))\"\r\n                                },\r\n                                {\r\n                                    \"#operator\": \"Clone\"\r\n                                },\r\n                                {\r\n                                    \"#operator\": \"Set\",\r\n                                    \"set_terms\": [\r\n                                        {\r\n                                            \"path\": \"(`route`.`old_equipment`)\",\r\n                                            \"value\": \"(`route`.`equipment`)\"\r\n                                        },\r\n                                        {\r\n                                            \"path\": \"(`route`.`equipment`)\",\r\n                                            \"value\": \"\\\"797\\\"\"\r\n                                        },\r\n                                        {\r\n                                            \"path\": \"(`route`.`updated`)\",\r\n                                            \"value\": \"true\"\r\n                                        }\r\n                                    ]\r\n                                },\r\n                                {\r\n                                    \"#operator\": \"SendUpdate\",\r\n                                    \"alias\": \"route\",\r\n                                    \"keyspace\": \"travel-sample\",\r\n                                    \"namespace\": \"default\"\r\n                                }\r\n                            ]\r\n                        }\r\n                    }\r\n                ]\r\n            }\r\n        },\r\n        {\r\n            \"#operator\": \"Discard\"\r\n        }\r\n    ]\r\n},\r\n<\/pre>\n
En la explicaci\u00f3n puede ver un operador NestedLoopJoin que se utiliza para la operaci\u00f3n ANSI JOIN (transformada internamente) entre el origen y el destino.<\/span><\/p>\n
Se puede especificar una sugerencia de uni\u00f3n en la fuente:<\/p>\n
MERGE INTO `travel-sample` AS route USING `travel-sample` AS airport USE HASH(build)\r\n  ON route.sourceairport = airport.faa AND airport.type = \"airport\"\r\n   AND route.type = \"route\"\r\nWHEN MATCHED THEN UPDATE SET route.old_equipment = route.equipment,\r\n                             route.equipment = \"797\", route.updated = true\r\n     WHERE route.airline = \"BA\" AND airport.country = \"France\"\r\n       AND CONTAINS(route.equipment, \"319\");<\/pre>\n
En este ejemplo se especifica la sugerencia USE HASH en el aeropuerto.<\/p>\n
Tratamiento de coincidencias m\u00faltiples en ANSI MERGE<\/span><\/h3>\n
En la fusi\u00f3n por b\u00fasqueda, como la condici\u00f3n de fusi\u00f3n es la clave del documento, s\u00f3lo hay una coincidencia potencial con el documento de destino para cada documento de origen. En la fusi\u00f3n ANSI, como la condici\u00f3n de fusi\u00f3n es ahora una expresi\u00f3n arbitraria, puede haber m\u00faltiples coincidencias de documentos de destino para cada documento de origen. Para cada acci\u00f3n de fusi\u00f3n especificada, la acci\u00f3n de fusi\u00f3n s\u00f3lo puede ejecutarse como m\u00e1ximo una vez para cada documento de origen. En caso de coincidencias m\u00faltiples, si se intenta una acci\u00f3n de fusi\u00f3n por segunda vez para el mismo documento fuente, se devolver\u00e1 un error (c\u00f3digo de error 5320 o 5330). Si se produce un error de este tipo, es necesario refinar la condici\u00f3n de fusi\u00f3n (cl\u00e1usula ON) para evitar coincidencias m\u00faltiples para el mismo documento fuente.<\/span><\/p>\n
Acci\u00f3n INSERT para ANSI MERGE<\/span><\/h3>\n
En la fusi\u00f3n por consulta, si se especifica una acci\u00f3n INSERT, s\u00f3lo se proporciona el documento que se est\u00e1 insertando. Dado que la fusi\u00f3n por b\u00fasqueda requiere una coincidencia en la clave del documento, y la acci\u00f3n INSERT s\u00f3lo se activa cuando no se encuentra ninguna coincidencia (es decir, no existe ning\u00fan documento con esa clave de documento en el destino), el nuevo documento insertado simplemente utilizar\u00e1 la misma clave de documento. En el caso de ANSI MERGE, esto ya no es cierto, puesto que ya no es necesario que una clave de documento sea la condici\u00f3n de coincidencia. En consecuencia, cuando se especifica una acci\u00f3n INSERT, tambi\u00e9n debe especificarse una clave de documento como parte de la acci\u00f3n INSERT. Por ejemplo:<\/span><\/p>\n
MERGE INTO `travel-sample` AS route USING `travel-sample` AS airport \r\n  ON route.sourceairport = airport.faa AND airport.type = \"airport\"\r\n   AND route.type = \"route\"\r\nWHEN NOT MATCHED THEN INSERT (KEY UUID(), VALUE {\"sourceairport\": airport.faa, \"targetairport\": \"SFO\", \"type\": \"route\"}) \r\n     WHERE airport.country = \"France\";\r\n<\/pre>\n
En comparaci\u00f3n, \u00e9sta es la sintaxis de la acci\u00f3n INSERT para look-up merge:<\/span><\/p>\n
WHEN NOT MATCHED THEN INSERT <expr><\/pre>\n
donde  es el documento que se va a insertar.<\/span><\/p>\n
Esta es la sintaxis de la acci\u00f3n INSERT para ANSI MERGE:<\/span><\/p>\n
WHEN NOT MATCHED THEN INSERT (<key_expr>, <value_expr>)\r\nWHEN NOT MATCHED THEN INSERT (KEY <key_expr>, VALUE <value_expr>)<\/pre>\n
donde  especifica la clave del documento reci\u00e9n insertado y  especifica el nuevo documento. Los dos est\u00e1n separados por una coma.<\/span><\/p>\n
Se pueden utilizar dos formas, las palabras clave KEY y VALUE son opcionales. Tambi\u00e9n se requiere un nuevo par de par\u00e9ntesis.<\/span><\/p>\n
Las dem\u00e1s acciones de fusi\u00f3n (acci\u00f3n UPDATE, acci\u00f3n DELETE) siguen siendo las mismas entre look-up merge y ANSI MERGE.<\/span><\/p>\n
 <\/p>\n
Resumen<\/span><\/h2>\n
Las mejoras para ANSI JOIN y el soporte para ANSI MERGE en Couchbase 6.5 mejoran el cumplimiento ANSI de N1QL, y hace que N1QL sea m\u00e1s f\u00e1cil de usar, especialmente para la migraci\u00f3n desde una base de datos relacional.<\/span><\/p>","protected":false},"excerpt":{"rendered":"
Introduction N1QL support for ANSI JOIN was first introduced in version 5.5. Currently there is a restriction in ANSI JOIN (version 5.5 and 6.0) in that the right-hand side of an ANSI JOIN must be a keyspace. This restriction will […]<\/p>","protected":false},"author":3021,"featured_media":13873,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[1816,1812],"tags":[],"ppma_author":[9065],"class_list":["post-8085","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-couchbase-server","category-n1ql-query"],"acf":[],"yoast_head":"\nANSI JOIN Enhancements and ANSI MERGE - The Couchbase Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.couchbase.com\/blog\/es\/ansi-join-enhancements-and-ansi-merge\/\" \/>\n<meta property=\"og:locale\" content=\"es_MX\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ANSI JOIN Enhancements and ANSI MERGE\" \/>\n<meta property=\"og:description\" content=\"Introduction N1QL support for ANSI JOIN was first introduced in version 5.5. Currently there is a restriction in ANSI JOIN (version 5.5 and 6.0) in that the right-hand side of an ANSI JOIN must be a keyspace. This restriction will […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.couchbase.com\/blog\/es\/ansi-join-enhancements-and-ansi-merge\/\" \/>\n<meta property=\"og:site_name\" content=\"The Couchbase Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-01-21T18:30:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-09-12T07:46:45+00:00\" \/>\n<meta name=\"author\" content=\"Bingjie Miao, Principal Software Engineer Couchbase\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bingjie Miao, Principal Software Engineer Couchbase\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutos\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\"},\"author\":{\"name\":\"Bingjie Miao, Senior Software Engineer, Couchbase\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/3a65595a167e56276aec1f708072c2e6\"},\"headline\":\"ANSI JOIN Enhancements and ANSI MERGE\",\"datePublished\":\"2020-01-21T18:30:47+00:00\",\"dateModified\":\"2024-09-12T07:46:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\"},\"wordCount\":1457,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"articleSection\":[\"Couchbase Server\",\"SQL++ \/ N1QL Query\"],\"inLanguage\":\"es\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\",\"name\":\"ANSI JOIN Enhancements and ANSI MERGE - The Couchbase Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"datePublished\":\"2020-01-21T18:30:47+00:00\",\"dateModified\":\"2024-09-12T07:46:45+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#breadcrumb\"},\"inLanguage\":\"es\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"es\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png\",\"width\":1800,\"height\":630},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.couchbase.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ANSI JOIN Enhancements and ANSI MERGE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"name\":\"The Couchbase Blog\",\"description\":\"Couchbase, the NoSQL Database\",\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"es\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\",\"name\":\"The Couchbase Blog\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"es\",\"@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\":\"es\",\"@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\/es\/author\/bingjie-miaocouchbase-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"ANSI JOIN Enhancements and ANSI MERGE - The Couchbase Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.couchbase.com\/blog\/es\/ansi-join-enhancements-and-ansi-merge\/","og_locale":"es_MX","og_type":"article","og_title":"ANSI JOIN Enhancements and ANSI MERGE","og_description":"Introduction N1QL support for ANSI JOIN was first introduced in version 5.5. Currently there is a restriction in ANSI JOIN (version 5.5 and 6.0) in that the right-hand side of an ANSI JOIN must be a keyspace. This restriction will […]","og_url":"https:\/\/www.couchbase.com\/blog\/es\/ansi-join-enhancements-and-ansi-merge\/","og_site_name":"The Couchbase Blog","article_published_time":"2020-01-21T18:30:47+00:00","article_modified_time":"2024-09-12T07:46:45+00:00","author":"Bingjie Miao, Principal Software Engineer Couchbase","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Bingjie Miao, Principal Software Engineer Couchbase","Est. reading time":"7 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#article","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/"},"author":{"name":"Bingjie Miao, Senior Software Engineer, Couchbase","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/3a65595a167e56276aec1f708072c2e6"},"headline":"ANSI JOIN Enhancements and ANSI MERGE","datePublished":"2020-01-21T18:30:47+00:00","dateModified":"2024-09-12T07:46:45+00:00","mainEntityOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/"},"wordCount":1457,"commentCount":0,"publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","articleSection":["Couchbase Server","SQL++ \/ N1QL Query"],"inLanguage":"es","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/","url":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/","name":"ANSI JOIN Enhancements and ANSI MERGE - The Couchbase Blog","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","datePublished":"2020-01-21T18:30:47+00:00","dateModified":"2024-09-12T07:46:45+00:00","breadcrumb":{"@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#breadcrumb"},"inLanguage":"es","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/"]}]},{"@type":"ImageObject","inLanguage":"es","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#primaryimage","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2022\/11\/couchbase-nosql-dbaas.png","width":1800,"height":630},{"@type":"BreadcrumbList","@id":"https:\/\/www.couchbase.com\/blog\/ansi-join-enhancements-and-ansi-merge\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.couchbase.com\/blog\/"},{"@type":"ListItem","position":2,"name":"ANSI JOIN Enhancements and ANSI MERGE"}]},{"@type":"WebSite","@id":"https:\/\/www.couchbase.com\/blog\/#website","url":"https:\/\/www.couchbase.com\/blog\/","name":"El blog de Couchbase","description":"Couchbase, la base de datos NoSQL","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":"es"},{"@type":"Organization","@id":"https:\/\/www.couchbase.com\/blog\/#organization","name":"El blog de Couchbase","url":"https:\/\/www.couchbase.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"es","@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, Ingeniero Superior de Software, Couchbase","image":{"@type":"ImageObject","inLanguage":"es","@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 es ingeniero de software principal en Couchbase. Bingjie tiene 20 a\u00f1os de experiencia en bases de datos relacionales y NoSQL. Su principal \u00e1rea de especializaci\u00f3n es la optimizaci\u00f3n y ejecuci\u00f3n de consultas.","url":"https:\/\/www.couchbase.com\/blog\/es\/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 es ingeniero de software principal en Couchbase. Bingjie tiene 20 a\u00f1os de experiencia en bases de datos relacionales y NoSQL. Su principal \u00e1rea de especializaci\u00f3n es la optimizaci\u00f3n y ejecuci\u00f3n de consultas."}],"_links":{"self":[{"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/posts\/8085","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/users\/3021"}],"replies":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/comments?post=8085"}],"version-history":[{"count":0,"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/posts\/8085\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/media\/13873"}],"wp:attachment":[{"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/media?parent=8085"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/categories?post=8085"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/tags?post=8085"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/es\/wp-json\/wp\/v2\/ppma_author?post=8085"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}