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

Transact-SQL (2008)

Difference

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2011-10-23 : 06:12:59

Hello Iam new. I have been working with Sql for 1 month.
I need help.I had an example. What is the difference between

SELECT productname, unitsinstock,CompanyName
FROM Products,Suppliers
WHERE CompanyName='tokyo traders'

SELECT productname, unitsinstock,CompanyName
FROM Products
Join Suppliers
on products.supplierid=suppliers.supplierid
WHERE CompanyName='tokyo traders'

SECOND example showed only tree records but the first one 77. why does first example show 77? how can we interpret this result?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-23 : 06:21:42

1st query, you are doing a CROSS JOIN.
the query is effectively same as


SELECT productname, unitsinstock,CompanyName
FROM Products CROSS JOIN Suppliers
WHERE CompanyName='tokyo traders'

when you cross join 2 tables, it is returning all permutation of records in both table. For 77 records return, one of the table must have 7 records and the other 11. 7 x 11 = 77

if you add in the condition, you will get the same result as 2nd query.


SELECT productname, unitsinstock,CompanyName
FROM Products,Suppliers
WHERE CompanyName='tokyo traders'
AND   Products.supplierid=Suppliers.supplierid

Your 2nd query is the preferred method of writing the query

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-23 : 11:17:26

read more about joins here

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources