Using Left Anti Join in SQL

Question

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!

Answer 1

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
)