Difference Queries with Same Results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Difference Queries with Same Results

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-06-25 : 04:24:17

Hi All,

Please let me know the difference between the following queries..
----------------------------------------------------------
Select Employee.* From Employee Inner Join Finmast On
Finmast.EmpID = Employee.EmpID Where FinMast.Status In(20,21,30)
----------------------------------------------------------
Select Employee.* From Employee Inner Join
(Select EmpID From FinMast Where FinMast.Status in (20,21,20) Finmast On Finmast.EmpID = Employee.EmpID

----------------------------------------------------------
Select Employee.* From Employee Inner Join FinMast
On Finmast.EmpID = Employee.EmpID And
FinMast.Status In (20,21,30)
----------------------------------------------------------

i checked through the execution plan all them returns the same time frame...

and all the time required for execution of this query is same...
so wHere does the differecnce lies.

Thanks

Complicated things can be done by simple thinking

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-25 : 10:43:38

The 3 queries change the speicification of the inner join'ed result set only slightly.

Query 1 joins the entire table and subsequently filters on the IN clause.

Query 2 joins on a single column of the table FinMast, but only values that satisfy the IN clause.

Query 3 joins the entire table, but only rows that satisfy the IN clause.

Query 1 and 3 may return different results if a left outer join was used.

Here's another

Select Employee.* From Employee
WHERE EmpID IN (Select EmpID from FinMast WHERE Status IN (20,21,30))

and another

SELECT * FROM Employee E
WHERE EXISTS (SELECT 1 FROM FinMast F WHERE F.EmpID = E.EmpID and F.Status IN (20,21,30)))

and another

SELECT * FROM Employee E
LEFT OUTER JOIN FinMast F ON F.EmpID = E.EmpID
WHERE F.Status IN (20,21,30) and F.Status IS NOT NULL

and another

SELECT * FROM Employee E
FULL OUTER JOIN FinMast F ON E.EmpID = F.EmpID
WHERE F.Status IN (20,21,30) AND E.EmpID IS NOT NULL AND F.EmpID IS NOT NULL

and another

SELECT * FROM Employee E
CROSS JOIN FinMast F
WHERE E.EmpID = F.EmpID AND F.Status IN (20,21,30)

andy8979
Starting Member

36 Posts

Posted - 2005-06-27 : 04:14:20

Hi,
Actually the execution plan shown is the fastest and the shortest execution path found by the complier, during the compilation it may vary for the same query depending on the number of records.
May be if you have more records in your table it will show some different execution plan.
if you want to use some specific indexes which are not being used in your query then you can specify the hint for the query to use the index. You can check out the bol for further specificaitons.

Anuj.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources