SQL Server Performance

SQL Select in( issue

Discussion in 'SQL Server 2005 General Developer Questions' started by chandis, Jun 25, 2009.

  1. chandis New Member

    Hi All,
    I have the following query.when I run this it does not return any rows.SELECT * from Table 1 where fieldx
    in(SELECT fieldx from table2 where key=27);when I hard code the values as SELECT * from Table 1 where fieldx
    in(4,5); it returns the data.what could be the reason for this.grately appriciate the help
    tx
    C
  2. FrankKalis Moderator

    Welcome to the forum!
    Can there be NULL values in table2? If so, the whole IN clause evaluates to NULL and no row is returned. I would rewrite the IN clause to a JOIN.
  3. atulmar New Member

    Also keep in mind that uniqueness of primary key column values in left table or right table would decide number of records returned in result set.
  4. Madhivanan Moderator

    [quote user="FrankKalis"]
    Welcome to the forum!
    Can there be NULL values in table2? If so, the whole IN clause evaluates to NULL and no row is returned. I would rewrite the IN clause to a JOIN.
    [/quote]
    It can return rows even if there are null values retured from subqueryselect
    * from
    (
    select 6 as n union all select 74
    ) as t1 where n in
    (
    select null as n union all select 6
    )
  5. FrankKalis Moderator

    [quote user="Madhivanan"]
    [quote user="FrankKalis"]
    Welcome to the forum!
    Can there be NULL values in table2? If so, the whole IN clause evaluates to NULL and no row is returned. I would rewrite the IN clause to a JOIN.
    [/quote]
    It can return rows even if there are null values retured from subquery
    select * from
    (
    select 6 as n union all select 74
    ) as t1 where n in
    (
    select null as n union all select 6
    )
    [/quote]
    Aargh, my bad. Was thinking of NOT IN. Thanks for correcting me.
  6. chandis New Member

    thanks for the reply.still the same issue.when I run the sub query for the union it returns the values 3,4..but the whole query return no rows.
  7. chandis New Member

    hi
    there are no null values in the second table.when I run a select in the second table it returns me 2,4..but when I include it in the in clouse no rows returned
  8. rohit2900 Member

    [quote user="chandis"]SELECT fieldx from table2 where key=27
    [/quote]
    Run this query and I'm almost certain then you will get your answer.....
  9. chandis New Member

    Hi
    the query returns 2,4..but the whole query has returned me 0 rows.still no clue why this is
  10. Sandy New Member

    [quote user="chandis"]SELECT * from Table 1 where fieldx
    in(SELECT fieldx from table2 where key=27);
    when I hard code the values as SELECT * from Table 1 where fieldx
    in(4,5); it returns the data.what could be the reason for this.grately appriciate the help[/quote]
    hey Chandi,
    If you go step by step, you can get your result. Let's start with the subquery SELECT fieldx from table2 where key=27, as you said it returns 2,4.. then the main query becomes SELECT * from Table 1 where fieldx in(2,4,....); Right?
    Then As you said when you applied SELECT * from Table 1 where fieldx in(4,5); It returns some data then the above query should returns data for key id 4
    Note: Please check again your script and if possible just post Table script and some sample data.(select statement)
    Thank you,
    Sandy.

Share This Page