OpenSearch:Join operation types

Left outer join:

• LEFT JOIN is the operator for left outer join operations. This type of join operation returns all data entries in 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

• Note If the values in some rows of the right table are duplicate, we recommend that you do not specify multiple consecutive left joins in the same statement. If you specify multiple consecutive left joins, data bloat may occur.

Inner join:

• INNER JOIN is the operator for inner join operations. When you specify the operator, you can specify only JOIN and omit the INNER keyword. An inner join operation returns the rows of data that match the ON condition.

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

• You can set the ON condition to TRUE. If you set the condition to TRUE, the system returns the Cartesian products that the system calculates based on the number of values in the specified fields in the tables. The SQL queries that are defined in the following sample code 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 is the operator for semi-join operations. When the system performs a semi-join operation, the system filters data in the left table based on the data in the right table. Data in the right table is not included in the dataset that the system returns after the system performs the semi-join operation.

• The system returns the rows of data that match the join condition from the left table. Example: If the value of the id field of a row in the tj_shop table is the same as a value of the id field in the tj_item table, the row of data is included in the result set.

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:

• ANTI JOIN is the operator for anti-join operations. When the system performs an anti-join operation, the system filters data in the left table based on the data in the right table. Data in the right table is not included in the dataset that the system returns after the system performs the anti-join operation.

• The system returns the rows of data that do not match the join condition from the left table. Example: If the value of the id field of a row in the tj_shop table is not a value of the id field in the tj_item table, the row of data is included in the result set.

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
)