Join operation types

Left outer join:

• A left outer join returns all rows from the left table, even if there is no matching row 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 right table contains duplicate values, avoid chaining too many left join clauses, as this can cause data bloat during the join.

Inner join:

• An inner join returns only the 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

• You can set the ON condition to always be TRUE, which 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 in the left table and does not appear in the result set.

• When the join condition is met, the matching rows from the left table are returned. If the id value of a row in tj_shop appears in any id value in tj_item, that row is kept 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:

• In an ANTI JOIN, the right table is used only to filter data in the left table and does not appear in the result set.

• When the join condition is not met, the rows from the left table are returned. If the id value of a row in tj_shop does not exist in any id value in tj_item, that row is kept 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
)