locked
Clarification required for 2 different SQL queries. RRS feed

  • Question

  • User-895102900 posted

    Hi friends,

    I dont have any circle  at circleid 101. when i am executing below queries, returning 2 differnt out puts. can you please tell me the difference between these 2 types of queries.

    declare @test int
    set @test =10
    select @test = circleid from system_circle where circleid=101
    select @test


    declare @test int
    set @test =10
    set @test =(select circleid from system_circle where circleid=101)
    select @test

    Wednesday, May 16, 2012 9:42 AM

Answers

  • User-1264638846 posted

    In first case the query against the system_circle table returns no rows because the value specified for circleid does not exist in the table. Then  select @test will display 10.

    In Second case, a subquery is used to assign a value to @test. Because the value requested for circleid does not exist, the subquery returns no value and the variable is set to NULL.   Then  select @test will display NULL.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 16, 2012 10:21 AM
  • User-784583665 posted

    here select @test = circleid from system_circle where circleid=101 it assigns value to @test only if there is value

     

    here SET @test = (select  circleid from system_circle where circleid=101) if no value comes then null is assigned

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 18, 2012 6:34 AM
  • User-1264638846 posted

    Second, so u will get actual status.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 25, 2012 8:56 AM

All replies

  • User-1264638846 posted

    In first case the query against the system_circle table returns no rows because the value specified for circleid does not exist in the table. Then  select @test will display 10.

    In Second case, a subquery is used to assign a value to @test. Because the value requested for circleid does not exist, the subquery returns no value and the variable is set to NULL.   Then  select @test will display NULL.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 16, 2012 10:21 AM
  • User-784583665 posted

    here select @test = circleid from system_circle where circleid=101 it assigns value to @test only if there is value

     

    here SET @test = (select  circleid from system_circle where circleid=101) if no value comes then null is assigned

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 18, 2012 6:34 AM
  • User-895102900 posted

    Please suggest me, which query is preferable always.

    Friday, May 25, 2012 5:26 AM
  • User-1264638846 posted

    Second, so u will get actual status.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 25, 2012 8:56 AM