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
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: 10585 Visits: 4199
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 (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68249 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
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 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
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: 10585 Visits: 4199
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 (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227395 Visits: 46338
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 (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68249 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
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: 10585 Visits: 4199
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 (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227395 Visits: 46338
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 (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68249 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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35974 Visits: 11361
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