Which method for SELECT is prefered? - 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 Programming

Which method for SELECT is prefered?

Author

Topic

Butterfly82
Starting Member

30 Posts

Posted - 2007-11-19 : 17:34:29

Hi guys,

I'm just curious if there is a prefered method when it comes to SELECT statements that include multiple tables. I have two versions:

SELECT FirstName, LastName, ProductName, SalePrice 
FROM SALES, PRODUCTS, CUSTOMERS 
WHERE MONTH(SaleDate) = '2' 
	AND YEAR(SaleDate) = '2005'
	AND SALES.ProductID = PRODUCTS.ProductID 
	AND SALES.CustomerID = CUSTOMERS.CustomerID;


SELECT FirstName, LastName,  ProductName, SalePrice
FROM CUSTOMERS
INNER JOIN SALES ON CUSTOMERS.CustomerID = SALES.CustomerID
INNER JOIN PRODUCTS ON PRODUCTS.ProductID = SALES.ProductID
WHERE MONTH(SaleDate) = '2' 
	AND YEAR(SaleDate) = '2005';

Could you tell me if either of these methods are favored over the other or if there is another way of producing the same output that is used over both these methods.

Thank you in advance

Butterfly82

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 17:42:05

Both of your versions are inefficient. Try this instead:


SELECT FirstName, LastName,  ProductName, SalePrice
FROM CUSTOMERS
INNER JOIN SALES 
ON CUSTOMERS.CustomerID = SALES.CustomerID
INNER JOIN PRODUCTS 
ON PRODUCTS.ProductID = SALES.ProductID
WHERE SalesDate >= '02-01-2005' AND SalesDate < '03-01-2005'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Butterfly82
Starting Member

30 Posts

Posted - 2007-11-19 : 17:49:55

tkizer, thanks for your reply.

So you would use the INNER JOIN over the first simple SELECT version. what is the reasoning behind this?

You also changed the way in which the WHERE clause is written, what makes this more efficent then using the MONTH and YEAR methods?

Sorry if these questions are basic.

Butterfly82

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-19 : 17:51:00

Yes I'd use the JOIN syntax since that's the standard.

And yes my solution is more efficient since your solution is unable to make use of indexes on your SalesDate column due to the functions being used.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Butterfly82
Starting Member

30 Posts

Posted - 2007-11-19 : 17:57:07

Thanks again tkizer, your reply is much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources