CODEWITHSUNDEEP
sqljoingoogle-bigqueryanti-join
realkes
realkes

Reputation: 881

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!

Upvotes: 1

Views: 23705

Answers (2)

karthikeya petluru
karthikeya petluru

Reputation: 1

With except clause, we can also achieve the same result:

select * from sessiontbl
except
select t1.session_id,t1.sender_id,t1.event_date
from sessiontbl t1 inner join transactiontbl t2
on t1.sender_id = t2.sender_id
where t1.event_date > t2.send_date

Upvotes: 0

The Impaler
The Impaler

Reputation: 48810

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
)

Upvotes: 3

Related Questions