SQL - IN statement
Hello ,
I have below query
The above does not work, the in statement is not working and it is not deleting the rows from tablex which are in @mcdid.
Please help.
I have below query
declare @mcdid table (
comparevalue varchar(20)
)
insert into @mcdid(comparevalue)
SELECT CDID FROM PerDiemFormulary WHERE FacID=7
delete from tableX where ndc in(select * from @mcdid )The above does not work, the in statement is not working and it is not deleting the rows from tablex which are in @mcdid.
Please help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
how is the IN different from EXISTS
it's essentially the same, as it is a condition where a single value must match the return set of a subquery. Although since EXISTS measures the existance of a value, it's somewhat faster.
IN can be done manually as a simple list though..
IN can be done manually as a simple list though..
SELECT *
FROM MyFruit
WHERE FruitType IN ('banana', 'orange', 'grape')
>>how is the IN different from EXISTS
they are quite different "under the hood"
the use of IN() is really a "syntax shortcut", using Jim's example:
WHERE FruitType IN ('banana', 'orange', 'grape')
is just a shortcut for the following (and is the EXACT equivalent of):
WHERE ( FruitType ='banana' OR FruitType ='orange' OR FruitType ='grape' )
so the contents of the parentheses is a series of OR statements within the where clause.
---------
"under the hood" an EXISTS() structure is a "semi-join", i.e. it is similar to a join but isn't quite the same, but due to this close relationship with joins using EXISTS() is often efficient.
--------
note, when using IN() with a limited range of values than it is just as efficient as EXISTS() and can be indistinguishable in performance terms. However with very large lists of variables performance of IN() can degrade quite markedly,
they are quite different "under the hood"
the use of IN() is really a "syntax shortcut", using Jim's example:
WHERE FruitType IN ('banana', 'orange', 'grape')
is just a shortcut for the following (and is the EXACT equivalent of):
WHERE ( FruitType ='banana' OR FruitType ='orange' OR FruitType ='grape' )
so the contents of the parentheses is a series of OR statements within the where clause.
---------
"under the hood" an EXISTS() structure is a "semi-join", i.e. it is similar to a join but isn't quite the same, but due to this close relationship with joins using EXISTS() is often efficient.
--------
note, when using IN() with a limited range of values than it is just as efficient as EXISTS() and can be indistinguishable in performance terms. However with very large lists of variables performance of IN() can degrade quite markedly,
DELETE FROM TableX WHERE ndc IN (SELECT DISTINCT comparevalue FROM @mcdid)