Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


join on 1=1


join on 1=1

Author
Message
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20937 Visits: 9671
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
SSC Veteran
SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)

Group: General Forum Members
Points: 262 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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

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

FROM SchoolProgram srp
INNER JOIN @Programs pr ON srp.ProgramID = 7
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20937 Visits: 9671
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10340 Visits: 11350
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