Syntax question using where instead of join

  • 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)

    ?

  • 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

  • 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.

  • 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

  • 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.

  • 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)

  • 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...

  • 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

  • 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

  • Sorry, I'll fix that. It should have been sample. Guess there was a little issue between the chair and keyboard.

  • 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