JOIN differences - 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

General SQL Server Forums

New to SQL Server Programming

JOIN differences

Author

Topic

rossmurphy
Starting Member

2 Posts

Posted - 2010-07-07 : 22:20:18

Can anybody tell me the difference between the following...


SELECT user_alias, user_id
FROM common_userprofile cup
INNER JOIN common_profilemap cpm ON cup.USERPROFILE_ID = cpm.USERPROFILE_ID 
WHERE cpm.SITE_ID IN (51, 52)
and cup.user_alias = 'Player11759'

-----


SELECT user_alias, user_id
FROM common_userprofile cup
WHERE common_profilemap cpm ON cup.USERPROFILE_ID = cpm.USERPROFILE_ID 
AND cpm.SITE_ID IN (51, 52)
AND user_alias = 'Player11759'

Cheers..

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-07-08 : 01:10:03

For the second query while parsing the parser will try to locate the column 'Useralias'
since it does not have a identifier which regards to the table.
It will find the underline table and then try to parse the remaining

later the further steps for parsing will be same.

Lets unLearn

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-08 : 02:34:00

The difference is that the first query should work and the second gives an error!

No, you're never too old to Yak'n'Roll if you're too young to die.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-08 : 17:08:16

the second query is not parsed. because under WHERE conditions one can not put a reference to table that should be defined under FROM statement.

rossmurphy
Starting Member

2 Posts

Posted - 2010-07-08 : 18:45:18

Ok my bad, I didnt have the second query correct. Here it is again... Is the second a default join? Which is more efficient?

SELECT user_alias, user_id
FROM common_userprofile cup
INNER JOIN common_profilemap cpm ON cup.USERPROFILE_ID = cpm.USERPROFILE_ID
WHERE cpm.SITE_ID IN (51, 52)
and cup.user_alias = 'Player11759'

-----

SELECT user_alias, user_id
FROM common_userprofile cup, common_profilemap cpm
WHERE cup.USERPROFILE_ID = cpm.USERPROFILE_ID
AND cpm.SITE_ID IN (51, 52)
AND cup.user_alias = 'Player11759'

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-08 : 23:15:45

the more efficient is the first one, because you tell the join where to join, where as the second one you tell this information in where clause along with other additional filters.

you can always analyse yourself the execution plan as well as use SET STATISTICS TIME ON|OFF command and SET STATISTICS IO ON|OFF to wrap around both queries.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 02:22:48

Both doing an INNER JOIN, they are identical.
The second syntax is depricated and not so well readable.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources