Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

join on 1=1 Expand / Collapse
Author
Message
Posted Wednesday, November 9, 2011 10:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 1,773, Visits: 3,207
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

Post #1203106
Posted Wednesday, November 9, 2011 10:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:00 AM
Points: 21,397, Visits: 9,612
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.
Post #1203110
Posted Wednesday, November 9, 2011 11:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 8, 2014 12:41 PM
Points: 262, Visits: 349
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".
Post #1203113
Posted Wednesday, November 9, 2011 11:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 1,773, Visits: 3,207
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
Post #1203120
Posted Wednesday, November 9, 2011 11:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1203121
Posted Wednesday, November 9, 2011 11:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:00 AM
Points: 21,397, Visits: 9,612
Well you could do it like this if you wanted to =>

FROM SchoolProgram srp
INNER JOIN @Programs pr ON srp.ProgramID = 7
Post #1203122
Posted Wednesday, November 9, 2011 11:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 1,773, Visits: 3,207
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.
Post #1203123
Posted Wednesday, November 9, 2011 11:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1203127
Posted Wednesday, November 9, 2011 11:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:00 AM
Points: 21,397, Visits: 9,612
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.
Post #1203128
Posted Thursday, November 10, 2011 12:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 11,194, Visits: 11,141
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1203304
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse