NOT EXISTS with multiple criteria and indirect unique id in SQL Server

I am having a hard time understanding this query with NOT EXISTS in SQL Server.

My colleague helped me with this query which works fine. There are 3 tables basically and I want to see if work_q_id exists in t_allocation table or not but t_allocation does not have a column (key) which matches the work_q_id that's why I had to join it with pick table. In short, it works like this:

work -> pick -> allocation

My question is how does NOT EXISTS know which work_q_id does not exist in t_allocation when t_allocation does not even have that column?

Pick table has work_q_id which was used to join with work table and t_allocation also has pick_id which I used to join with pick table to be able to identify the work_q_ids that do not exist.

Can someone please help me understand the concept?

Thanks in advance

select 
    wkq.wh_id, wkq.work_q_id
from 
    work wkq with (NOLOCK)
inner join 
    pick pkd WITH (NOLOCK) on pkd.work_q_id = wkq.work_q_id
                           and pkd.wh_id = wkq.wh_id
where 
    wkq.work_type in ('72')
    and wkq.work_status = 'U'
    and (wkq.pick_ref_number like ('%PICK_PRIME%')
         or wkq.description like 'TopOff%')
    and pkd.status = 'RELEASED'
    --and (CONVERT(VARCHAR , CONVERT(DATETIME , wkq.datetime_stamp AT TIME ZONE ('UTC') AT TIME ZONE ('Eastern Standard Time')) , 120)) <= '2021-05-26 15:33:00' 
    and (CONVERT(VARCHAR , CONVERT(DATETIME , wkq.datetime_stamp AT TIME ZONE ('UTC') AT TIME ZONE ('Eastern Standard Time')) , 120)) <= DATEADD(hh, -24, (CONVERT(VARCHAR , CONVERT(DATETIME , GETDATE() AT TIME ZONE ('UTC') AT TIME ZONE ('Eastern Standard Time')) , 120)))
    and not exists (select 1 from t_allocation alc WITH (NOLOCK)
                    WHERE pkd.wh_id = alc.wh_id 
                AND pkd.pick_id = alc.pick_id
                AND pkd.work_q_id = wkq.work_q_id)

Solution

In your query, the EXISTS is a correlated subquery: in other words, it has one or more outer references to the rest of the query.

Looking just at the relevant parts:

from 
    work wkq
inner join 
    pick pkd......

So wkq and pkd are the outer table references

where 
....
    not exists (select 1

What you actually select in an EXISTS query is irrelevant, so 1 is fine, NOT inverts the EXISTS obviously

 from t_allocation alc WITH (NOLOCK)

Now alc is the inner reference

                    WHERE pkd.wh_id = alc.wh_id 
                AND pkd.pick_id = alc.pick_id

These two lines are correlations between the inside reference and the outside reference, so the EXISTS will only pick out rows that match these conditions

                AND pkd.work_q_id = wkq.work_q_id)

This is interesting. It is not a correlation, because there are no inner references, only outer.

It is a startup filter: the EXISTS will only return rows if this outer-reference condition is true