whats the difference between...

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)

whats the difference between...

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2003-11-25 : 15:28:07

What is the difference between these 2 queries, to me they are the same (which scares me!)

Query#1

select *
from table#1 t1
INNER JOIN table#2 t2 ON (t1.myID = t2.myID AND t2.myFK = 3)

Query#2

SELECT *
FROM table#1 t1
INNER JOIN table#2 t2 ON (t1.myID = t2.myID)
WHERE t2.myFK = 3

TIA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-25 : 15:53:35

They return the same results when you are doing an INNER JOIN; when you do OUTER JOIN's they are very different.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-25 : 15:57:58

Check out:


USE Northwind
GO
    SELECT * 
      FROM Orders a 
 LEFT JOIN [Order Details] b 
	ON a.OrderId = b.OrderId 
     WHERE Quantity = 12

    SELECT * 
      FROM Orders a 
 LEFT JOIN [Order Details] b 
	ON a.OrderId = b.OrderId 
       AND Quantity = 12
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-25 : 16:56:30

Some more info for you, if you're interested:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=30567

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources