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

OpenSearch High-performance Search Edition supports four join types for combining data across tables: left outer join, inner join, semi-join, and anti-join.

Join type	Keyword	Returns
Left outer join	`LEFT JOIN`	All rows from the left table, including rows with no match in the right table
Inner join	`[INNER] 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 the rows that do not match the rows 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 in the join column, avoid chaining multiple LEFT JOIN clauses in the same statement. Multiple consecutive left joins on a table with duplicate values can cause data bloat.

Inner join

[INNER] JOIN returns only the rows that satisfy the ON condition. The INNER keyword is optional — JOIN alone is equivalent to INNER JOIN.

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 the two 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

Syntax: SEMI JOIN

A semi-join filters the left table based on the right table. Only rows from the left table that have a matching row in the right table are returned. The right table is not included in the result set.

For example, a row from tj_shop is returned if its id value matches any id value in tj_item.

Semi-joins are expressed using subqueries. The following two forms are equivalent:

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
)

Anti-join

Syntax: ANTI JOIN

An anti-join filters the left table based on the right table. Only rows from the left table that have no matching row in the right table are returned. The right table is not included in the result set.

For example, a row from tj_shop is returned if its id value does not match any id value in tj_item.

Anti-joins are expressed using subqueries. The following two forms are equivalent:

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
)