MaxCompute supports two types of SEMI JOIN operations: LEFT SEMI JOIN and LEFT ANTI JOIN. In SEMI JOIN, the right table does not appear in the result set and is only used to filter data in the left table.
SEMI JOIN supports MAPJOIN hints that can improve the performance of LEFT SEMI JOIN and LEFT ANTI JOIN. For more information, see MAPJOIN hint.
LEFT SEMI JOIN
A LEFT SEMI JOIN
returns all rows from the left table that matches a row in the right table. For example,
if the id
value of a row in mytable1
appears in the id
column in mytable2
, this row is returned in the result set.
SELECT * FROM mytable1 a LEFT SEMI JOIN mytable2 b ON a.id=b.id;
In this example, only rows in mytable1
with IDs that appear in mytable2
are returned in the result set.
LEFT ANTI JOIN
A LEFT ANTI JOIN
returns all rows from the left table that does not have a match in the right table.
For example, if the id
value of a row in mytable1
does not appear in the id
column in mytable2
, this row is returned in the result set.
SELECT * FROM mytable1 a LEFT ANTI JOIN mytable2 b ON a.id=b.id;
In this example, only rows in mytable1
with IDs that do not appear in mytable2
are returned in the result set.