CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Adrian
Adrian

Reputation: 836

SQL, how do I convert to logic ('not in' to 'not exists')

Delete From StudentTb Where StudentType in (1, 2) and StudentI not in
(
    Select lg.StudentI From StudentLog lg
)

I tried to changed the above code to the following... But, I am not sure if it is correct or not.

Delete From StudentTb Where StudentType in (1, 2) 
    and Not Exists 
    (
        Select 'x' 
        From StudentLog lg                          
        Where StudentI= lg.StudentI
    )

Can anyone please help me?

Upvotes: 2

Views: 677

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146557

Delete From StudentTb  s
Where StudentType in (1, 2) 
and Not Exists 
(
    Select *
    From StudentLog                          
    Where StudentI = s.StudentI
)

In a subquery, unqualified references to columns(which do not specify table name or alias) are first assumed to be in the table referenced in the subquery itself, not in other tables in the outer query. So, in your sql syntax, both sides of the predicate Where StudentI = lg.StudentI are the same column in the StudentLog subquery table.

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

Not in and not exists do not always have the same meaning. I assume you want to convert because "not in" tends to be slow. Here is another way where the logic will always match.

Delete From StudentTb 
Where StudentType in (1, 2) 
and StudentI in
(
select StudentI
from StudentTb
except 
Select StudentI From StudentLog    
)

Upvotes: 1

Yosi Dahari
Yosi Dahari

Reputation: 7009

All you did wrong is not specifying the right alias in the subquery:

Delete From StudentTb Where StudentType in (1, 2) 
    and Not Exists 
    (
        Select 'x' 
        From StudentLog lg                          
        Where StudentTb.StudentI= lg.StudentI
    )

Note - the columns in the select statement doesn't affect the final outcome, but it's still preferred to write * instead of 'x'

Upvotes: 2

Related Questions