InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 12:19:30 GMT20http://www.sqlservercentral.com/Forums/Topic1383236-392-1.aspx[quote][b]GilaMonster (11/12/2012)[/b][hr][quote][b]SQLSACT (11/12/2012)[/b][hr]When using [i]in[/i] adding a correlation predicate is redundant?[/quote]Written correctly, just about all the time. Please read that blog post and maybe BoL on how the IN function works[quote]Am I on track here?[code="sql"]select T1.Col1 from T1where T1.Col1 in(select Col1 from T2 where T2.Col1 = T1.Col1)[/code][/quote]No, I gave you the correct form in my previous post.[quote]--2select T1.Col1 from T1where T1.Col1 in (select Col1 from T2)[/quote]Think about what the IN does with the two columns specified and you should understand why the correlation is completely unnecessary.[/quote]ThanksI see now why it's unnecessaryWhen I asked "am I on track here" followed by the query. I was just confirming that [i]that[/i] query was incorrect based on my Layman's terms.ThanksMon, 12 Nov 2012 12:50:53 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1383236-392-1.aspx[quote][b]SQLSACT (11/12/2012)[/b][hr]When using [i]in[/i] adding a correlation predicate is redundant?[/quote]Written correctly, just about all the time. Please read that blog post and maybe BoL on how the IN function works[quote]Am I on track here?[code="sql"]select T1.Col1 from T1where T1.Col1 in(select Col1 from T2 where T2.Col1 = T1.Col1)[/code][/quote]No, I gave you the correct form in my previous post.[quote]--2select T1.Col1 from T1where T1.Col1 in (select T2.Col1 from T2)[/quote]Think about what the IN does with the two columns specified and you should understand why the correlation is completely unnecessary.Mon, 12 Nov 2012 12:41:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1383236-392-1.aspx[quote][b]GilaMonster (11/12/2012)[/b][hr]Well, those aren't written correctly. The correct form would be[code="sql"]--1select T1.Col1 from T1where exists (select * from T2 where T2.Col1 = T1.Col1)--2select T1.Col1 from T1where T1.Col1 in(select Col1 from T2)[/code]And those two are completely equivalent.[url]http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url][/quote]Thanks GailI think I'm getting thereWhen using [i]in[/i] adding a correlation predicate is redundant?In my own words, using the query below. The subquery has already satisfied what I'm looking therefore rendering the outer query unnecessary. Something like [i]select Col1 from table1 where table1 in (select Col1 from table1 )[/i]Am I on track here?[code="sql"]select T1.Col1 from T1where T1.Col1 in(select Col1 from T2 where T2.Col1 = T1.Col1)[/code]ThanksMon, 12 Nov 2012 11:51:05 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1383236-392-1.aspxWell, those aren't written correctly. The correct form would be[code="sql"]--1select T1.Col1 from T1where exists (select * from T2 where T2.Col1 = T1.Col1)--2select T1.Col1 from T1where T1.Col1 in(select T2.Col1 from T2)[/code]And those two are completely equivalent.[url]http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url]Mon, 12 Nov 2012 10:56:49 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1383236-392-1.aspx[quote][b]Sean Lange (11/9/2012)[/b][hr]I would write that query a little differently. I would instead write it as[code]select t1.Col1 from Tbl1 t1join Tbl2 t2 on t1.Col1 = t2.Col2where t1.Col1 = '52'[/code]That to me is a LOT easier to read and understand quickly. Check out the execution plans for both queries. It will really surprise you!!![/quote]Thanks SeanI'm just trying to better understand T-SQLWhen dealing with correlated subqueries, are these 2 queries essentially the same?[code="sql"]--1select T1.Col1 from T1where exists (select Col1 from T2 where T2.Col1 = T1.Col1)--2select T1.Col1 from T1where T1.Col1 in(select Col1 from T2 where T2.Col1 = T1.Col1)[/code]ThanksSat, 10 Nov 2012 13:34:05 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1383236-392-1.aspxI would write that query a little differently. I would instead write it as[code]select t1.Col1 from Tbl1 t1join Tbl2 t2 on t1.Col1 = t2.Col2where t1.Col1 = '52'[/code]That to me is a LOT easier to read and understand quickly. Check out the execution plans for both queries. It will really surprise you!!!Fri, 09 Nov 2012 14:54:55 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1383236-392-1.aspx[quote][b]Roland Alexander STL (11/9/2012)[/b][hr]Maybe, maybe not. Depends on many factors such as indexing, clustering, number of rows in the tables, etc. Check the execution plan to see what's getting processed when. Might surprise you![/quote]Thanks for the replyI want to be able to completely understand what is fundamentally happening hereThe subquery has its results and the outer query has its own results as well, are these 2 results cross referenced?ThanksFri, 09 Nov 2012 14:31:12 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1383236-392-1.aspxMaybe, maybe not. Depends on many factors such as indexing, clustering, number of rows in the tables, etc. Check the execution plan to see what's getting processed when. Might surprise you!Fri, 09 Nov 2012 14:19:34 GMTRoland Alexander STLhttp://www.sqlservercentral.com/Forums/Topic1383236-392-1.aspxHi guysTrying to gear up my T-SQL skillsWhen dealing with subqueries, like below[code="sql"]select Col1 from Tbl1 where Col1 = '52' and Col1 in (select Col2 from Tbl2)[/code]What is the order of the execution here? Does SQL first check for the value 52 in Tbl2.Col2 and then check for the value 52 in Tbl1.Col1ThanksFri, 09 Nov 2012 14:16:13 GMTSQLSACT