sqljoingoogle-bigqueryanti-join

Using Left Anti Join in SQL

I have a session table like (table1):

session ID sender ID event date
s1 s1 2020-10-02
s2 s1 2020-10-06
s3 s2 2020-03-01
s4 s2 2020-03-02
s5 s3 2020-08-02
s6 s4 2020-02-02

And a transactions table (table2) like:

transaction ID sender ID send date
t1 s1 2020-10-01
t2 s1 2020-10-05
t3 s2 2020-04-01
t4 s3 2020-07-02
t5 s4 2020-12-12

I want to generate a table that only contains the sessions of the users who have not made any transaction before their session date. According to the above tables, I want to return:

session ID sender ID event date
s3 s2 2020-03-01
s4 s2 2020-03-02
s6 s4 2020-02-02

I want to solve this using Anti-Join. Would the below code work for this purpose?

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.sender_id = t1.sender_id 
    AND t2.event_date > t1.event_date
WHERE t2.sender_id IS NULL

Please feel free to suggest any method other than anti-join. Thanks!

Solution

  • You can use EXISTS as in:

    select *
from t1
where not exists (
  select 1 
  from t2 
  where t2.sender_id = t1.sender_id and t2.send_date < t1.event_date
)