Join operation types-OpenSearch(Open Search)-阿里云帮助中心

OpenSearch Retrieval Engine Edition supports four join types for querying across multiple tables.

Left outer join, semi-join, and anti-join require OpenSearch Retrieval Engine Edition V3.7.5 or later. Inner join is available in all versions.

Supported join types

Join type	Operator	Returns
Left outer join	`LEFT JOIN`	All rows from the left table, including rows with no match in the right table
Inner join	`JOIN`	Only rows that satisfy the `ON` condition
Semi-join	`SEMI JOIN`	Rows from the left table that have a matching row in the right table
Anti-join	`ANTI JOIN`	Rows from the left table that have no matching row in the right table

Left outer join

LEFT JOIN returns all rows from the left table, including rows with no match in the right table.

SELECT
  t1.id, t2.id
FROM
  tj_shop AS t1
LEFT JOIN
  tj_item AS t2
ON
  t1.id = t2.id

Warning

If the right table contains duplicate values, avoid chaining multiple consecutive LEFT JOIN operations in the same statement — this can cause data bloat.

Inner join

JOIN returns only the rows that match the ON condition. Omit the INNER keyword; use JOIN alone.

SELECT
  t1.id, t2.id
FROM
  tj_shop AS t1
JOIN
  tj_item AS t2
ON
  t1.id = t2.id

Setting the ON condition to TRUE returns the Cartesian product of both tables. The following two queries return the same result:

SELECT
  t1.id, t2.id
FROM
  tj_shop AS t1
JOIN
  tj_item AS t2
ON
  TRUE

SELECT
  t1.id, t2.id
FROM
  tj_shop, tj_item;

Semi-join

SEMI JOIN filters rows from the left table based on the right table. It returns only left table rows that have a matching row in the right table — right table columns are not included in the result.

Use WHERE id IN (subquery) or WHERE EXISTS (subquery) to express a semi-join:

SELECT
  id
FROM
  tj_shop
WHERE id IN (
  SELECT
    id
  FROM
    tj_item
)

SELECT
  id
FROM
  tj_shop
WHERE EXISTS (
  SELECT
    id
  FROM
    tj_item
  WHERE
    tj_shop.id = id
)

Both queries return rows from tj_shop whose id value appears in tj_item.id.

Anti-join

ANTI JOIN filters rows from the left table based on the right table. It returns only left table rows that have no matching row in the right table — right table columns are not included in the result.

Use WHERE id NOT IN (subquery) or WHERE NOT EXISTS (subquery) to express an anti-join:

SELECT
  id
FROM
  tj_shop
WHERE id NOT IN (
  SELECT
    id
  FROM
    tj_item
)

SELECT
  id
FROM
  tj_shop
WHERE NOT EXISTS (
  SELECT
    id
  FROM
    tj_item
  WHERE
    tj_shop.id = id
)

Both queries return rows from tj_shop whose id value does not appear in tj_item.id.