InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 04:25:30 GMT20http://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspx[quote][b]Dev @ +91 973 913 6683 (11/10/2011)[/b][hr][quote]It's basically doing a cross join. Maybe the author didn't know about that option.[/quote]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 ;-) )[/quote]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.Thu, 10 Nov 2011 04:48:51 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspx[quote][b]sqlfriends (11/9/2011)[/b][hr]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[/quote]You can produce CROSS join with any of the following:[code="sql"]select a.*, b.*from a,b[/code][code="sql"]select a.*,b.*from a CROSS JOIN b[/code][code="sql"]select a.*,b.*from a INNER JOIN bon 1=1 -- fake condition to satisfy INNER JOIN syntax.[/code]The results would be same until you add any filters.Thu, 10 Nov 2011 00:35:36 GMTDevhttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspx[quote]It's basically doing a cross join. Maybe the author didn't know about that option.[/quote]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 ;-) )Thu, 10 Nov 2011 00:29:59 GMTDevhttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspx[quote][b]sqlfriends (11/9/2011)[/b][hr]Thanks, so above statement, for inner join it doesn't need to join on a matched column?[/quote]That's right, the SQL language doesn't force people to write joins that make sense.[quote]I just feel it's kind of hard for me to understand this cross join.[/quote]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.Thu, 10 Nov 2011 00:23:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspxAs 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.Wed, 09 Nov 2011 11:22:57 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspx[quote][b]sqlfriends (11/9/2011)[/b][hr][quote][b]Ninja's_RGR'us (11/9/2011)[/b][hr]Well you could do it like this if you wanted to =>FROM SchoolProgram srpINNER JOIN @Programs pr ON srp.ProgramID = 7[/quote]Thanks, so above statement, for inner join it doesn't need to join on a matched column? [/quote]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 srpCROSS JOIN @Programs pr WHERE srp.ProgramID = 7Wed, 09 Nov 2011 11:21:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspx[quote][b]Ninja's_RGR'us (11/9/2011)[/b][hr]Well you could do it like this if you wanted to =>FROM SchoolProgram srpINNER JOIN @Programs pr ON srp.ProgramID = 7[/quote]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.Wed, 09 Nov 2011 11:19:06 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspxWell you could do it like this if you wanted to =>FROM SchoolProgram srpINNER JOIN @Programs pr ON srp.ProgramID = 7Wed, 09 Nov 2011 11:15:04 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspx[quote][b]sqlfriends (11/9/2011)[/b][hr]so if I change the join to cross join and take off 1=1, it will be the same, correct?[/quote]Yes[quote]But I read somewhere if a cross join have a where clause, it is the same as inner join, [/quote]Depends what the where clause is.Wed, 09 Nov 2011 11:14:07 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspxso 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?ThanksWed, 09 Nov 2011 11:10:10 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspxI 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".Wed, 09 Nov 2011 11:01:48 GMTJohn Michael Robertsonhttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspxIt'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.Wed, 09 Nov 2011 10:57:07 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1203106-392-1.aspxI'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 31INSERT INTO @Programs SELECT 32INSERT INTO SchoolProgram(SchoolYear,SchoolID,RangeCd,ProgramID,...)SELECT srp.SchoolYear, srp.SchoolID, srp.RangeCd, pr.ProgramID, srp.OriginalProjection, srp.RevisedProjection, ...FROM SchoolProgram srpJOIN @Programs pr ON 1 = 1WHERE srp.ProgramID = 7Wed, 09 Nov 2011 10:50:38 GMTsqlfriends