October 9, 2014 at 4:25 am
Hi,
Pls suggest me which is best one in below two queries.
Create Table ABC(name varchar(1))
Insert into ABC select 'A'
Insert into ABC select 'B'
Insert into ABC select 'C'
Create Table CDE(name varchar(1))
Insert into CDE select 'B'
1) select * from ABC where name not in(select name from CDE)
2) select * from ABC where name not in('B')
October 9, 2014 at 4:54 am
The second query only accesses one table, so it will likely use less resources. But if you expand the NOT IN list with more values there would be a break-even point. The list will be expanded to multiple OR statements. At some point I guess you could better rewrite the query to a LEFT OUTER JOIN:
SELECT ABC.*
FROM ABC
LEFT OUTER JOIN CDE
ON ABC.name = CDE.name
WHERE CDE.name IS NULL
October 9, 2014 at 4:58 am
You could also use NOT EXISTS, which should perform better than NOT IN:
SELECT *
FROM ABC
WHERE NOT EXISTS (SELECT 1 FROM CDE WHERE ABC.name = CDE.name);
Regarding your original question: the second query has hardcoded input, which I would try to avoid at all costs.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 9, 2014 at 5:50 am
Just so we're clear, those really are two very different queries. One references a single table. The other references two tables.
I agree with the other comments, but I think we need to make this clear. We're not comparing two similar objects.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply