Post reply

subquery in the column value of a SELECT

  • September 21, 2005 at 4:24 pm

    #166082

    I have a table TEMP1 with the following data:

    create table temp1 (empno int primary key, detail varchar(20))

    insert into temp1 (empno, detail) values (1, 'aaaaa')

    insert into temp1 (empno, detail) values (2, 'bbbbb')

    insert into temp1 (empno, detail) values (3, 'ccccc')

    insert into temp1 (empno, detail) values (4, 'ddddd')

    insert into temp1 (empno, detail) values (5, 'eeeee')

    When I execute the following query how many times does the subquery for the second column maxempno get executed? Only once for all the 5 rows in the table or once for every row? That is once or five times?

    select empno,
             (select max(t2.empno) from temp1 t2) maxempno

    from temp1

    where empno < 6

     

  • September 21, 2005 at 7:38 pm

    #592059

    Run the query in Query Analyzer with "Show Execution Plan" selected and you see

     

    --------------------
    Colt 45 - the original point and click interface

  • September 22, 2005 at 9:03 am

    #592261

    Under the execution plan, for "stream aggregate" the value for "Estimated number of executes" is 1. So, I am assuming the subquery is getting executed only once. I hope I am reading it correctly.

    Thanks.

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

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