Post reply

Invalid Sql returns all rows

  • April 2, 2014 at 9:41 am

    #289992

    I discovered this today and was hoping someone had an explanation. I forgot that a table I was attempting to do a lookup on didn't have the foreign key. I was really surprised when it started returning all rows. I have an example below. I don't even know why it compiles.

    Create table #Table1 (

    mykey int

    )

    Create table #BadLookup (

    notmykey int

    )

    insert into #Table1

    select 1 union all

    select 2 union all

    select 3

    insert into #BadLookup

    select 1

    select * from #Table1 where mykey in (select mykey from #BadLookup)

    Drop table #Table1

    Drop table #BadLookup

  • April 2, 2014 at 9:46 am

    #1702531

    If you used 2-part names for your columns, it should become clear.

    select *

    from #Table1 t1

    where t1.mykey in (select t1.mykey from #BadLookup bl)

    It might look weird that the subquery is using a column from the outer query, but it's expected functionality and the base to correlated subqueries.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 2, 2014 at 10:11 am

    #1702543

    Interesting. After a quick search for correlated sub queries I understand why this is expected. Thanks for the help and another tool in my belt.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply