use DolphinDB to delete rows with matching columns with table join

Question

Now I have two tables t1 and t2 as shown in the screenshot. I'd like to select the rows where both columns a and b in the left match those of the right table. Then delete them in the left table. Would you please inform me if any DolphinDB functions can implement this operation?

Answer 1

Suppose the two tables are t1 and t2.

The left table is t1, containing columns a, b and c; The right table is t2, containing columns a and b.

There are two ways in DolphinDB to obtain the desired output:

Method 1:

select t1.*,t2.* from lj(t1, t2, `a`b) where isNull(t2.a)

or Method 2:

s = set(string(t2.a)+string(t2.b))
select * from t1 where not string(t1.a)+string(t1.b) in s