What is the difference? - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

What is the difference?

Author

Topic

Namagiri
Starting Member

5 Posts

Posted - 2013-03-25 : 12:21:59

UPDATE OD
SET discount += 0.05
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid= O.orderid
WHERE O.custid= 1;

UPDATE OD
SET discount += 0.05
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid= O.orderid
and O.custid= 1;

Thanks

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-03-25 : 12:41:53

They look similar , in terms of the resultset you'll see. Althougfh the syntax is different i.e WHERE versus = .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-25 : 13:09:38

In the first query, you are using a WHERE clause to apply the predicate "O.custid= 1." Where as, with the second query, you are applying the predicate on the join clause. For an INNER JOIN there isn't a difference in the result set. However, if you do an OUTER join, where you apply the predicate can make a difference in the result set.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-26 : 03:19:14

Check these links for getting knowledge on LEFT JOIN with WHERE clause....

http://www.codeproject.com/Articles/231132/Difference-between-And-clause-along-with-on-and-Wh
http://blog.sqlauthority.com/2009/03/15/sql-server-interesting-observation-of-on-clause-on-left-join-how-on-clause-effects-resultset-in-left-join/

Namagiri
Starting Member

5 Posts

Posted - 2013-03-26 : 15:28:57

Thanks everyone. Thanks bandi for the links.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-27 : 01:16:14