Usage of NOT EXISTS / NOT IN operator

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 228
Find out the IDs which doesn't have PENDING status.
Here the 123 ID is having PENDING status. So it shouldn't be in the final output

--Sample Data population
DECLARE @temp TABLE (status varchar(10), id int)
INSERT INTO @temp
SELECT 'Processed', 123
UNION ALL
SELECT 'complete', 123
UNION ALL
SELECT 'pending', 123
UNION ALL
SELECT 'Processed', 456
UNION ALL
SELECT 'complete', 456

-- Query to get the records whihc doesn't have status as 'pending'
SELECT status, ID
FROM @temp
WHERE status != 'Pending'
/*
Processed 123
complete 123
Processed 456
complete 456
*/

The above query gives incorrect results as 123 ID is haing pending status.

To resolve this issue, the below two approaches will work fine.

-- First Approach
SELECT status, ID
FROM @temp
WHERE ID NOT IN (SELECT ID from @temp where status = 'pending')

-- second approach
select *
from @temp a
where NOT EXISTS
(
SELECT *
FROM @temp x
WHERE x.id = a.id
AND x.status = 'pending'
)

/* OUTPUT
status id
Processed 456
complete 456
*/

Comments or Responses

Login to post response