JOIN operation types - OpenSearch - Alibaba Cloud Documentation Center

OpenSearch supports four types of JOIN operations: left join, inner join, semi join, and anti join.

Left outer join

A left join returns all records from the left table, even if there are no matching records 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

Note

If the values in the right table are not unique, avoid using too many consecutive left join operations to prevent data bloat during the JOIN procedure.

Inner join

An inner join returns rows that satisfy the ON condition. The inner keyword is optional.

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

If the ON condition is always TRUE, the operation returns the Cartesian product of the two tables. The following two SQL statements are equivalent.

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

In a semi join, the right table is used only to filter data from the left table and does not appear in the result set.
When the join condition is met, the operation returns data from the left table. A row from the tj_shop table is returned if its id exists in the tj_item table.

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

In an anti join, the right table is used only to filter data from the left table and does not appear in the result set.
When the join condition is not met, the operation returns data from the left table. A row from the tj_shop table is returned if its id does not exist in the tj_item table.

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
)