MaxCompute supports LEFT SEMI JOIN and LEFT ANTI JOIN to filter rows from a left table based on whether a matching row exists in the right table. This topic describes their syntax and provides usage examples.
How it works
MaxCompute supports the following SEMI JOIN operations:
LEFT SEMI JOIN
Returns rows from the left table that have a matching row in the right table. This is equivalent to an IN subquery.
LEFT ANTI JOIN
Returns rows from the left table that do not have a matching row in the right table. This is similar to, but not identical to, a NOT IN subquery.
Both operations support the MAPJOIN hint to improve performance.
Limitations
You can reference the right table only in the join condition (the ON clause), not in the WHERE or SELECT clauses.
Sample data
-- Create a partitioned table named sale_detail.
CREATE TABLE IF NOT EXISTS sale_detail (
shop_name STRING,
customer_id STRING,
total_price DOUBLE
)
PARTITIONED BY (
sale_date STRING,
region STRING
);
CREATE TABLE IF NOT EXISTS sale_detail_sj (
shop_name STRING,
customer_id STRING,
total_price DOUBLE
)
PARTITIONED BY (
sale_date STRING,
region STRING
);
-- Add partitions to the source tables.
ALTER TABLE sale_detail ADD PARTITION (sale_date='2026', region='china');
ALTER TABLE sale_detail_sj ADD PARTITION (sale_date='2026', region='china');
-- Insert data into the source tables.
INSERT INTO sale_detail PARTITION (sale_date='2026', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail_sj PARTITION (sale_date='2026', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2),('s2','c2',100.2);
Usage examples
LEFT SEMI JOIN example
This example finds all rows in the sale_detail table that have a matching total_price in the sale_detail_sj table.
SELECT * FROM sale_detail a
LEFT SEMI JOIN sale_detail_sj b
ON a.total_price = b.total_price;
-- Returned result:
+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
| s1 | c1 | 100.1 | 2026 | china |
| s2 | c2 | 100.2 | 2026 | china |
+-----------+-------------+-------------+-----------+--------+Returns only rows from sale_detail that have a matching total_price in sale_detail_sj.
LEFT ANTI JOIN example
This example finds all rows in the sale_detail table that do not have a matching total_price in the sale_detail_sj table.
SELECT * FROM sale_detail a
LEFT ANTI JOIN sale_detail_sj b
ON a.total_price = b.total_price;
-- Returned result:
+-----------+-------------+-------------+-----------+--------+
| shop_name | customer_id | total_price | sale_date | region |
+-----------+-------------+-------------+-----------+--------+
| s3 | c3 | 100.3 | 2026 | china |
+-----------+-------------+-------------+-----------+--------+Returns only the rows from sale_detail that do not have a matching total_price in sale_detail_sj.