MaxCompute:SEMI JOIN

MaxCompute supports the following types of SEMI JOIN operations:

• LEFT SEMI JOIN

  A LEFT SEMI JOIN operation returns rows from the left table that has matching rows in the right table. For example, if one row of data in the left table meets a specified condition and the data appears in the right table, the data is included in the result set.

  In MaxCompute, the usage of LEFT SEMI JOIN is similar to that of IN SUBQUERY. For more information about IN SUBQUERY, see IN SUBQUERY. You can choose one of the two operations.

• LEFT ANTI JOIN

  A LEFT ANTI JOIN operation returns rows from the left table that does not have matching rows in the right table. For example, if one row of data in the left table meets a specified condition and the data does not appear in the right table, the data is included in the result set.

  In MaxCompute, the usage of LEFT ANTI JOIN is similar to that of NOT IN SUBQUERY. For more information about NOT IN SUBQUERY, see NOT IN SUBQUERY.

Sample source data is provided for you to better understand the examples in this topic. The following statements describe how to create the sale_detail and sale_detail_sj tables and insert data into the tables.

-- 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 sale_detail table. 
alter table sale_detail add partition (sale_date='2013', region='china');
alter table sale_detail_sj add partition (sale_date='2013', region='china');

-- Insert data into the sale_detail table. 
insert into sale_detail partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
insert into sale_detail_sj partition (sale_date='2013', region='china') values ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2),('s2','c2',100.2);

Query data in the sale_detail and sale_detail_sj table,Sample statement:

set odps.sql.allow.fullscan=true;
select * from sale_detail;
-- The following result is returned: 
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+
select * from sale_detail_sj;
-- The following result is returned: 
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s5         | c2          | 100.2       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+