April 17, 2009 at 2:12 am
Dear Friends,
I have one query like
select * from emp where dept_no in(10,20,30)
Can anyone help me in using EXISTS instead of IN ?
Pls.....
Thanks in Advance
Mithun
April 17, 2009 at 2:31 am
I think you should use the EXISTS ( when you have complex query to retrieve the data you want from DB) instead of IN clause, because you can see differences here :
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE EXISTS
(SELECT *
FROM HumanResources.Department AS d
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.EmployeeID = edh.EmployeeID
AND d.Name LIKE 'P%');
GO
--------------------------------------------------------------------
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID
WHERE edh.DepartmentID IN
(SELECT DepartmentID
FROM HumanResources.Department
WHERE Name LIKE 'P%');
GO
Both queries search by name starting with P% in HR dep. but the results are different for 1 record!
April 17, 2009 at 2:38 am
Hi,
Also Try this simple codes
declare @sample table ( id int, Dept varchar(30))
insert into @sample
select 1,'10' union all
select 2,'20' union all
select 3,'30' union all
select 4,'40' union all
select 5,'50' union all
select 6,'60'
select * from @sample
where Dept in ('10','20','30')
RESULT
idDept
110
220
330
select a.* from @sample a
where exists (select 1 from @sample b where b.Dept = a.Dept and b.Dept in ('10','20','30'))
RESULT
idDept
110
220
330
ARUN SAS
April 17, 2009 at 2:43 am
mithun.gite (4/17/2009)
Dear Friends,I have one query like
select * from emp where dept_no in(10,20,30)
Can anyone help me in using EXISTS instead of IN ?
Pls.....
Thanks in Advance
Mithun
SELECT *
FROM emp a
WHERE EXISTS (SELECT 1 FROM emp WHERE dept_no = a.dept_no AND dept_no IN (10,20,30))
WHERE EXISTS isn't useful in this situation. It's useful where the main query and the subquery are against different tables, or there are different filters on the same table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 17, 2009 at 2:51 am
Dear ALL,
Actually I have been asked to Optimize the query and do the performance tunning to the query.
select * from emp where dept_no in(10,20,30)
As in this query only optimization we can do is using Exists instead of In but to use Exists we have to use IN in the subquery....
So will it increase the performance using IN in subquery?
Mithun
April 17, 2009 at 2:57 am
mithun.gite (4/17/2009)
Dear ALL,Actually I have been asked to Optimize the query and do the performance tunning to the query.
select * from emp where dept_no in(10,20,30)
As in this query only optimization we can do is using Exists instead of In but to use Exists we have to use IN in the subquery....
So will it increase the performance using IN in subquery?
Mithun
see the below link:
http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx
April 17, 2009 at 3:10 am
Dear All,
I took some test data on adventureworks database...
below are the queries:::
Query 1
select * from HumanResources.EmployeeDepartmentHistory
where departmentid in(7,4,1,2)
(201 row(s) affected)
Table 'EmployeeDepartmentHistory'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Query 2
select * from HumanResources.EmployeeDepartmentHistory
where exists(select 1 from HumanResources.EmployeeDepartmentHistory b where
HumanResources.EmployeeDepartmentHistory.departmentid=b.departmentid and
b.departmentid in (7,4,1,2))
(201 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'EmployeeDepartmentHistory'. Scan count 5, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 1 ms.
There is a big performnace problem with using Exists in this example,, so can i say that if we are passing static values in where clause then its better to use IN rather then using Exists..
Please guide me and make my concept clear......
Thansk
Mithun
April 17, 2009 at 3:27 am
mithun.gite (4/17/2009)
There is a big performnace problem with using Exists in this example,, so can i say that if we are passing static values in where clause then its better to use IN rather then using Exists..Mithun
Of course there is. It doesn't make sense to use EXISTS in this case and I don't think you should draw any conclusions from the test. The correlated subquery in query2 still uses IN, all you've done is wrap it in a layer of unnecessary complexity.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 17, 2009 at 3:39 am
Chris Morris (4/17/2009)
mithun.gite (4/17/2009)
There is a big performnace problem with using Exists in this example,, so can i say that if we are passing static values in where clause then its better to use IN rather then using Exists..Mithun
Of course there is. It doesn't make sense to use EXISTS in this case and I don't think you should draw any conclusions from the test. The correlated subquery in query2 still uses IN, all you've done is wrap it in a layer of unnecessary complexity.
can we get the same results wihtout using corelated query and wihtour using IN in it?
actually this is my confusiosn....
Mithun
April 17, 2009 at 3:53 am
mithun.gite (4/17/2009)
Actually I have been asked to Optimize the query and do the performance tunning to the query.select * from emp where dept_no in(10,20,30)
As in this query only optimization we can do is using Exists instead of In but to use Exists we have to use IN in the subquery....
There's no sensible way to use EXISTS in that query, and it's not a silver bullet for performance tuning anyway.
You say you've been asked to optimise that query. Why? How long does it take, how long is it supposed to take? Any indexes on the emp table? Is that SELECT * really necessary?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2009 at 4:13 am
GilaMonster (4/17/2009)
mithun.gite (4/17/2009)
Actually I have been asked to Optimize the query and do the performance tunning to the query.select * from emp where dept_no in(10,20,30)
As in this query only optimization we can do is using Exists instead of In but to use Exists we have to use IN in the subquery....
There's no sensible way to use EXISTS in that query, and it's not a silver bullet for performance tuning anyway.
You say you've been asked to optimise that query. Why? How long does it take, how long is it supposed to take? Any indexes on the emp table? Is that SELECT * really necessary?
See, anyone who sees the query can guess that its not the actual query to optimize,, its just a matter of concept of " Exists"....which i m not clear about... so wanted to know how we can use exists here and does it make sense in this example.....
this is waht i have been saying thru out the post......
Mithun
April 17, 2009 at 4:22 am
mithun.gite (4/17/2009)
GilaMonster (4/17/2009)
mithun.gite (4/17/2009)
Actually I have been asked to Optimize the query and do the performance tunning to the query.select * from emp where dept_no in(10,20,30)
As in this query only optimization we can do is using Exists instead of In but to use Exists we have to use IN in the subquery....
There's no sensible way to use EXISTS in that query, and it's not a silver bullet for performance tuning anyway.
You say you've been asked to optimise that query. Why? How long does it take, how long is it supposed to take? Any indexes on the emp table? Is that SELECT * really necessary?
See, anyone who sees the query can guess that its not the actual query to optimize,, its just a matter of concept of " Exists"....which i m not clear about... so wanted to know how we can use exists here and does it make sense in this example.....
this is waht i have been saying thru out the post......
Mithun
Mithun, can you post the actual query which you've been asked to optimize? It may be more suitable for demonstrating the use of EXISTS.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 17, 2009 at 4:32 am
mithun.gite (4/17/2009)
See, anyone who sees the query can guess that its not the actual query to optimize,,
Then what was the point of posting it?
its just a matter of concept of " Exists"....which i m not clear about... so wanted to know how we can use exists here and does it make sense in this example.....
No and no. It doesn't make any sense here. Exists is for when you want to check if a row exists in another table. It's not a replacement for IN with a value list.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2009 at 4:44 am
GilaMonster (4/17/2009)
mithun.gite (4/17/2009)
See, anyone who sees the query can guess that its not the actual query to optimize,,Then what was the point of posting it?
its just a matter of concept of " Exists"....which i m not clear about... so wanted to know how we can use exists here and does it make sense in this example.....
No and no. It doesn't make any sense here. Exists is for when you want to check if a row exists in another table. It's not a replacement for IN with a value list.
Chill,chilllllll,,,,,
In fact i got my answer that Exists is for when you want to check if a row exists in another table. It's not a replacement for IN with a value list.
its not the case that we can replace IN with EXISTS in all cases.......
And Asking for apology if have wasted ur time and if any mis behaviour from my side...
Thanks
Mithun
April 17, 2009 at 4:56 am
mithun.gite (4/17/2009)
GilaMonster (4/17/2009)
mithun.gite (4/17/2009)
See, anyone who sees the query can guess that its not the actual query to optimize,,Then what was the point of posting it?
its just a matter of concept of " Exists"....which i m not clear about... so wanted to know how we can use exists here and does it make sense in this example.....
No and no. It doesn't make any sense here. Exists is for when you want to check if a row exists in another table. It's not a replacement for IN with a value list.
Chill,chilllllll,,,,,
In fact i got my answer that Exists is for when you want to check if a row exists in another table. It's not a replacement for IN with a value list.
its not the case that we can replace IN with EXISTS in all cases.......
And Asking for apology if have wasted ur time and if any mis behaviour from my side...
Thanks
Mithun
Can you post your real query so we can get to work on it?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply