Where Left or Right - any difference? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Where Left or Right - any difference?

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2007-07-06 : 10:44:20

Is there any difference between these 2 SQL statements?...

Select TableA.*,
 (Select SUM(TableB.Column2) FROM TableB WHERE TableB.ChildIDColumn = TableA.ParentIDColumn) AS BTotal
FROM TableA

Select TableA.*,
 (Select SUM(TableB.Column2) FROM TableB WHERE TableA.ParentIDColumn = TableB.ChildIDColumn) AS BTotal
FROM TableA

sshelper
Posting Yak Master

216 Posts

Posted - 2007-07-06 : 10:47:40

There's no difference between those 2 SQL statements. You will get the same result.

SQL Server Helper
http://www.sql-server-helper.com

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2007-07-06 : 10:52:29

Thank You.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-06 : 11:06:45

Neither statement is good SQL. Try one of these methods:


Select	TableA.Column1,
	TableA.Column2,
	...
	TableA.ColumnN,
	Subtotals.BTotal
from	TableA
	left outer join
		(select TableB.ChildIDColumn,
			SUM(TableB.Column2) as BTotal
		From	TableB
		Group by TableB.ChildIDColumn) Subtotals
		on TableA.ParentIDColumn = Subtotals.ChildIDColumn

Select	TableA.Column1,
	TableA.Column2,
	...
	TableA.ColumnN,
	SUM(TableB.Column2) as BTotal
from	TableA
	left outer join TableB on TableA.ParentIDColumn = TableB.ChildIDColumn
group by TableA.Column1,
	TableA.Column2,
	...
	TableA.ColumnN

Avoid puttin subqueries in the SELECT clause. It can lead to inefficient execution plans.

e4 d5 xd5 Nf6

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-06 : 11:13:31

quote:

Select TableA.Column1,
TableA.Column2,
...
TableA.ColumnN,
Subtotals.BTotal
from TableA
left outer join
(select TableB.ChildIDColumn,
SUM(TableB.Column2) as BTotal
From TableB
Group by TableB.ChildIDColumn) Subtotals
on TableA.ParentIDColumn = Subtotals.ChildIDColumn

Select TableA.Column1,
TableA.Column2,
...
TableA.ColumnN,
SUM(TableB.Column2) as BTotal
from TableA
left outer join TableB on TableA.ParentIDColumn = TableB.ChildIDColumn
group by TableA.Column1,
TableA.Column2,
...
TableA.ColumnN

Grouping on every column in a table is not good SQL, either. Always go with the derived table (your first example) if you are grouping on long lists of columns, especially those that don't logically correlate with the results you want to return.

see:

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2007-07-06 : 11:32:24

I agree and I like your SQL.

However, how can I handle a Parent and Child aggregate with a Join? Let's say the Loads Column contains the value 1 for a Parent Record and there are 3 Child Records. When I use your join SQL the Result Set returns 3 for Total_Loads, and that is not right.

Select TableA.DateColumn,SUM(TableA.Loads) AS Total_Loads,
((Select SUM(TableB.Weight) FROM TableB WHERE TableB.ChildIDColumn = TableA.ParentIDColumn) AS Weight_Total
FROM TableA
GROUP BY TableA.DateField

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-06 : 12:22:22

quote:
Originally posted by jsmith8858

Grouping on every column in a table is not good SQL, either.

Agree except where the parent table has only a few columns, which is why I included both examples.

e4 d5 xd5 Nf6

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-06 : 12:24:12

Ken --

Did you try to implement the technique he showed you? It completely handles exactly what you describe. Look at it carefully, make sure it makes logical sense what he is doing, and try it.

- Jeff
http://weblogs.sqlteam.com/JeffS

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2007-07-06 : 14:11:59

No I did not, but I just did, and yes I am wrong. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources