July 8, 2009 at 8:11 am
I know that using a join is optimal, but for a weird problem I am having does this:
SELECT Table1.field1, table2.field1
FROM table1, table2
where table1.CustID = table2.CustID
equal this:
SELECT Table1.field1, table2.field1
FROM table1 INNER JOIN
table2 ON (table1.CustID = table2.CustID)
?
July 8, 2009 at 8:32 am
Yep. Those are equivalent.
They might become different in a more complex query, but generally the engine will treat the two the same.
Where it matters more is outer joins, instead of inner joins. In outer joins, the difference between the On statement and the Where clause can make huge differences in your results.
Because of that, it's generally better to use On statements for that, because it helps you achieve more consistency. Not an actual difference in results, just more consistent coding style.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 8, 2009 at 8:55 am
Just my 2 cents, but I really prefer using joins. I like the separation of join criteria from filter criteria. Makes reading code much easier.
July 8, 2009 at 8:58 am
Lynn Pettis (7/8/2009)
Just my 2 cents, but I really prefer using joins. I like the separation of join criteria from filter criteria. Makes reading code much easier.
I have to agree with that. Does make it more evident what it's about.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 8, 2009 at 9:12 am
I agree joins are better. I was just trying to solve a problem. Sadly, I got the same error when I moved the JOIN criteria to the WHERE clause. Frustrating.
Anyway, thanks.
July 8, 2009 at 3:09 pm
You know, I get tripped up here a lot.
Say that you have departments which have budget codes for every budget type reassigned every budget year. You want a
-complete list of departments,
-and the budget code for supplies budget,
-but list the department even if the supplies budget code hasn't been created
dbo.Department
DepartmentID
DepartmentName
dbo.BudgetType
BudgetTypeID
BudgetTypeName
dbo.BudgetCode
DepartmentID
BudgetTypeID
BudgetCode
FinancialYear
Which is better?
Select
...
From
dbo.Department d
Left Join (dbo.BudgetCode bc
Inner Join dbo.BudgetType bt
On bc.BudgetTypeID = bt.BudgetTypeID
And FinancialYear = @FinancialYear
And BudgetTypeName = 'Supplies')
On d.DepartmentID = bt.DepartmentID
OR
dbo.Department d
Left Join (dbo.BudgetCode bc
Inner Join dbo.BudgetType bt
On bc.BudgetTypeID = bt.BudgetTypeID)
On d.DepartmentID = bt.DepartmentID
Where (bt.BudgetTypeName = Supplies
OR bt.BudgetTypeName IS NULL)
And (bc.FinancialYear = @FinancialYear
OR bt.FinancialYear is NULL)
July 8, 2009 at 3:13 pm
middletree (7/8/2009)
I agree joins are better. I was just trying to solve a problem. Sadly, I got the same error when I moved the JOIN criteria to the WHERE clause. Frustrating.Anyway, thanks.
What is the error you are getting? You may want to post the table DDL, some sample data, and the code you have currently written to get better help.
Edit: correct typo...
July 9, 2009 at 7:13 am
Lynn, "maple data"???
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2009 at 11:15 am
Maple data is better than honey data 🙂
Of the last two supplied queries, my preference would be for the first. That said, it would be best to do as Lynn asked (sample data and ddl, and any errors that are thrown from each of the two queries).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 9, 2009 at 11:23 am
Sorry, I'll fix that. It should have been sample. Guess there was a little issue between the chair and keyboard.
July 13, 2009 at 8:19 am
Sorry I didn't respond earlier. I was out of town. As it turns out, the error I was getting was not related to my syntax as I thought it was. So I'm going a different route to fix this.
thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply