Code Difference, is one better? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 Administration

Code Difference, is one better?

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-23 : 09:52:03

I have two seperate SQL statements. Both retrieve the same data and both work. As you can see from the code they are written differently from each other.

SELECT DISTINCT
a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [msiexec],
d.Operating_System_Name_and0,
c.FilePath

FROM v_GS_SoftwareFile c INNER JOIN
v_GS_COMPUTER_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN
v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN
v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE (c.FileName = 'iexplore.exe') AND (c.FileVersion like '7.%' )
ORDER BY a.Name0

---------------------------------------------------------------------------------------------------------

not knowing SQL at a deep level in this code below I assume INNER JOIN is used even though it is not actually written? and is there any reason you would not use the coding style below? I like it since it makes sense to my minds-eye. Or is this only good for INNER JOINS but if you need an OUTER JOIN you need to use the method listed above?

Also, how does this work without any JOIN statements?

SELECT DISTINCT
v_GS_COMPUTER_SYSTEM.Name0 AS [PC Name],
v_R_System.Operating_System_Name_and0 AS [OS],
v_GS_SoftwareFile.FileVersion AS [File Version],
v_GS_SoftwareFile.FilePath AS [File Path]

From
v_GS_Computer_System, v_R_System, v_GS_SoftwareFile , v_FullCollectionMembership

WHERE
v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID AND
v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SoftwareFile.resourceID AND
v_GS_COMPUTER_SYSTEM.ResourceID = v_FullCollectionMembership.ResourceID AND
v_GS_SoftwareFile.FileName = 'iexplore.exe' AND
v_GS_SoftwareFile.FileVersion LIKE '7.%'
ORDER BY [PC Name]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-23 : 09:59:07

Makes no difference. Best is what's more readable - I would go for:


SELECT DISTINCT 
		[Machine Name]	= a.Name0 ,
		msiexec			= b.SiteCode,c.FileVersion,
		d.Operating_System_Name_and0,
		c.FilePath
FROM v_GS_SoftwareFile c
	JOIN v_GS_COMPUTER_SYSTEM a
		ON c.ResourceID = a.ResourceID
	JOIN v_R_System d
		ON a.ResourceID = d.ResourceID
	JOIN v_FullCollectionMembership b
		ON a.ResourceID = b.ResourceID
WHERE c.FileName = 'iexplore.exe'
AND c.FileVersion like '7.%'
ORDER BY a.Name0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 10:18:42

and to answer your second part you need to use ANSI style for OUTER JOIN ie LEFT OUTER JOIN,RIGHT OUTER JOIN etc as against


..
table1 t1,table2 t2
where t2.col *= t1.col
...

as *=,=* is no more supported in t-sql

also ANSI style adds more clarity to your code as your joins are explicit with all conditions included in corresponding ON condition

using old style joins can be confusing when you've multiple conditions for join condition and there are lots of tables involved in joins

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources