Post reply

join on 1=1

  • November 9, 2011 at 10:50 am

    #390816

    I'm reviewing the sql code of others, I know what it does, but not sure how to understand about the join on 1=1 means?

    The sql is basically to insert some rows into based on some existing rows in the same table, but just change the programID.

    DECLARE@Programs TABLE

    (

    ProgramID INT

    )

    INSERT INTO @Programs SELECT 30

    INSERT INTO @Programs SELECT 31

    INSERT INTO @Programs SELECT 32

    INSERT INTO SchoolProgram

    (

    SchoolYear,

    SchoolID,

    RangeCd,

    ProgramID,

    ...)

    SELECTsrp.SchoolYear,

    srp.SchoolID,

    srp.RangeCd,

    pr.ProgramID,

    srp.OriginalProjection,

    srp.RevisedProjection,

    ...

    FROMSchoolProgram srp

    JOIN@Programs pr ON 1 = 1

    WHEREsrp.ProgramID = 7

  • November 9, 2011 at 10:57 am

    #1405387

    It's basically doing a cross join. Maybe the author didn't know about that option.

    Outer apply might be my choice here depending on the real requirements.

  • November 9, 2011 at 11:01 am

    #1405389

    I have seen this used many times as well. It basically satisifies the syntax requirement of the "JOIN" clause without any joining fields. Produces same result as a "cross join".

  • November 9, 2011 at 11:10 am

    #1405396

    so if I change the join to cross join and take off 1=1, it will be the same, correct?

    But I read somewhere if a cross join have a where clause, it is the same as inner join,

    is it true for this case?

    Thanks

  • November 9, 2011 at 11:14 am

    #1405397

    sqlfriends (11/9/2011)

    so if I change the join to cross join and take off 1=1, it will be the same, correct?

    Yes

    But I read somewhere if a cross join have a where clause, it is the same as inner join,

    Depends what the where clause is.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 9, 2011 at 11:15 am

    #1405398

    Well you could do it like this if you wanted to =>

    FROM SchoolProgram srp

    INNER JOIN @Programs pr ON srp.ProgramID = 7

  • November 9, 2011 at 11:19 am

    #1405399

    Ninja's_RGR'us (11/9/2011)

    Well you could do it like this if you wanted to =>

    FROM SchoolProgram srp

    INNER JOIN @Programs pr ON srp.ProgramID = 7

    Thanks, so above statement, for inner join it doesn't need to join on a matched column?

    I just feel it's kind of hard for me to understand this cross join.

  • November 9, 2011 at 11:21 am

    #1405402

    sqlfriends (11/9/2011)

    Ninja's_RGR'us (11/9/2011)

    Well you could do it like this if you wanted to =>

    FROM SchoolProgram srp

    INNER JOIN @Programs pr ON srp.ProgramID = 7

    Thanks, so above statement, for inner join it doesn't need to join on a matched column?

    To be a proper join, it does. Ninja's example is not a join, it's a filter on a cross join, It's this:

    FROM SchoolProgram srp

    CROSS JOIN @Programs pr

    WHERE srp.ProgramID = 7

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 9, 2011 at 11:22 am

    #1405403

    As Gail said.

    Both queries will give the correct results.

    However, for readability I'll preffer cross join or cross apply in this situation. It makes it super obvious of what's going on.

  • November 10, 2011 at 12:23 am

    #1405549

    sqlfriends (11/9/2011)

    Thanks, so above statement, for inner join it doesn't need to join on a matched column?

    That's right, the SQL language doesn't force people to write joins that make sense.

    I just feel it's kind of hard for me to understand this cross join.

    I'm not surprised. Writing joins naturally, with proper join conditions in the ON clause and other non-join filters in the WHERE clause makes life easier for everyone. If it were me, I would rewrite these using explicit CROSS JOIN syntax.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 10, 2011 at 12:29 am

    #1405551

    It's basically doing a cross join. Maybe the author didn't know about that option.

    I haven't seen any instance when people use 1=1 by mistake. It's deliberate CROSS JOIN (just to skip code review tools / team leads 😉 )

  • November 10, 2011 at 12:35 am

    #1405552

    sqlfriends (11/9/2011)

    so if I change the join to cross join and take off 1=1, it will be the same, correct?

    But I read somewhere if a cross join have a where clause, it is the same as inner join,

    is it true for this case?

    Thanks

    You can produce CROSS join with any of the following:

    select a.*, b.*

    from a,b

    select a.*,b.*

    from a CROSS JOIN b

    select a.*,b.*

    from a INNER JOIN b

    on 1=1 -- fake condition to satisfy INNER JOIN syntax.

    The results would be same until you add any filters.

  • November 10, 2011 at 4:48 am

    #1405630

    Dev @ +91 973 913 6683 (11/10/2011)

    It's basically doing a cross join. Maybe the author didn't know about that option.

    I haven't seen any instance when people use 1=1 by mistake. It's deliberate CROSS JOIN (just to skip code review tools / team leads 😉 )

    That 1 would actually have been stopped by my review, not the cross join.

    Reviews are reviews, no matter what you put in the code.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply