SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


join on 1=1


join on 1=1

Author
Message
sqlfriends
sqlfriends
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27688 Visits: 4451
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,
...)
SELECT srp.SchoolYear,
srp.SchoolID,
srp.RangeCd,
pr.ProgramID,
srp.OriginalProjection,
srp.RevisedProjection,
...
FROM SchoolProgram srp
JOIN @Programs pr ON 1 = 1
WHERE srp.ProgramID = 7
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153890 Visits: 9672
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.
John Michael Robertson
John Michael Robertson
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 357
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".
sqlfriends
sqlfriends
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27688 Visits: 4451
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)

Group: General Forum Members
Points: 548063 Visits: 47738
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


Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153890 Visits: 9672
Well you could do it like this if you wanted to =>

FROM SchoolProgram srp
INNER JOIN @Programs pr ON srp.ProgramID = 7
sqlfriends
sqlfriends
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27688 Visits: 4451
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)

Group: General Forum Members
Points: 548063 Visits: 47738
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


Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153890 Visits: 9672
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.
Paul White
Paul White
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79480 Visits: 11400
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search