Need explanation for this.

• ksatpute123

  Hall of Fame

  Points: 3325

  More actions

  October 9, 2015 at 10:39 am

  #301340

  create table #Security

  (

  id int primary key identity(1,1),

  [Security Id] varchar(20),

  )

  insert into #Security([Security Id])

  select 'Sec12345' union all

  select 'Sec12346' union all

  select 'Sec12347' union all

  select 'Sec12348' union all

  select 'Sec12349' union all

  select 'Sec12350' union all

  select 'Sec12351' union all

  select 'Sec12352' union all

  select 'Sec12353' union all

  select 'Sec12354' union all

  select 'Sec12355' union all

  select 'Sec12356' union all

  select 'Sec12357' union all

  select 'Sec12358' union all

  select 'Sec12359' union all

  select 'Sec12360' union all

  select 'Sec12361' union all

  select 'Sec12362' union all

  select 'Sec12363' union all

  select 'Sec12364' union all

  select 'Sec12365'

  create table #SystemMappedSecurity

  (

  map_id int primary key identity(1,1),

  SecurityId varchar(20),

  SystemId int

  )

  insert into #SystemMappedSecurity(SecurityId,SystemId)

  select 'Sec12356',2 union all

  select 'Sec12362',1 union all

  select 'Sec12345',1 union all

  select 'Sec12349',3

  -- Column name in subquery should be [SecurityId]

  select * from #Security where [Security Id] in (select distinct [Security Id] from #SystemMappedSecurity)

  Here I am expecting an sql error 'Invalid Column Name', but there is no error and sql server executes the query without any problems and all the records from #Security table are returned. Why such odd behaviour?

  Does anyone have an explanation for this? I am running this query in sql server 2012.

• ScottPletcher

  SSC Guru

  Points: 101042

  More actions

  October 9, 2015 at 10:42 am

  #1832597

  A subquery can refer to a column in the outer/main query. Thus, SQL is comparing the column to itself. You need to prefix the column with the local table alias to make sure you are referencing a column in the local table.

  Edit: That is:

  select s.* from #Security s where s.[Security Id] in (select distinct sms.[Security Id] from #SystemMappedSecurity sms)

  Then you'll get the error you expect, and can correct it.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  October 9, 2015 at 11:17 am

  #1832609

  ScottPletcher (10/9/2015)

  ---

  A subquery can refer to a column in the outer/main query. Thus, SQL is comparing the column to itself. You need to prefix the column with the local table alias to make sure you are referencing a column in the local table.

  Edit: That is:

  select s.* from #Security s where s.[Security Id] in (select distinct sms.[Security Id] from #SystemMappedSecurity sms)

  Then you'll get the error you expect, and can correct it.

  This is one reason I like to alias all my tables and refer to the columns using the alias for all of them. I've been bitten by this exact situation myself.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• ksatpute123

  Hall of Fame

  Points: 3325

  More actions

  October 9, 2015 at 11:30 am

  #1832612

  ScottPletcher (10/9/2015)

  ---

  A subquery can refer to a column in the outer/main query. Thus, SQL is comparing the column to itself. You need to prefix the column with the local table alias to make sure you are referencing a column in the local table.

  Edit: That is:

  select s.* from #Security s where s.[Security Id] in (select distinct sms.[Security Id] from #SystemMappedSecurity sms)

  Then you'll get the error you expect, and can correct it.

  Thanks for explaining this in such simple words. Now I will do the same for my fellow colleagues. 🙂

• ScottPletcher

  SSC Guru

  Points: 101042

  More actions

  October 9, 2015 at 12:01 pm

  #1832626

  You're welcome!

  Ooh, one last interesting thing. If both the "local" table and the "outer" table have a column with that name, SQL will use the "local" ("closer") table. For example:

  CREATE TABLE #a ( col1 int, col2 int ) INSERT INTO #a VALUES(1,1),(2,2);

  CREATE TABLE #b ( col1 int ) INSERT INTO #b VALUES(1);

  SELECT * FROM #a a WHERE col1 IN (SELECT col1 FROM #b b);

  --will list only col1=1, since that's all that appears in b.col1. But this:

  SELECT * FROM #a a WHERE col2 IN (SELECT col2 FROM #b b);

  --will list both 1 and 2; since #b doesn't contain col2, it's comparing to itself, as we've seen.

  DROP TABLE #a

  DROP TABLE #b

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".