SQL Server Performance

Query question

Discussion in 'General Developer Questions' started by icata, Aug 26, 2003.

  1. icata New Member

    Hi,

    I have the following queries:

    create table t1(c1 int)

    insert into t1 values(1)
    insert into t1 values(2)
    insert into t1 values(3)

    select (select c1 where c1 = 1 )
    from t1


    select (select c1 from t1 where c1 = 1 )
    from t1

    How come the first select works when on BOL is saying:

    "The FROM clause is required except when the select list
    contains only constants, variables, and arithmetic
    expressions (no column names)."

    And if it is ok why I don't get the same result from both queries?

    Ionel
  2. vbkenya New Member

    The query optimizer interprets the first query as a left outer join between the constant value (1) and the table 't1':

    It virtually creates a table with one column and one row containing the value 1 (lets call it 't2' and the column 'c2') and then performs the equivalent of the following query:

    Select c2 from t1 left join t2 on
    t2.c2=t1.c1

    Despite BOL, the from clause may not be required here because c1 is a valid column name in table t1 and that is why the join occurs. If you had used a non-existent column name, SQL Server would have thrown an error.

    The second query is interpreted the same way as the first except that instead of one constant value there are three(1,1,1) values -one for each record in t1 - being left joined with the table t1.

    Note: The query execution plan will not reflect the statements above, but essentially that is what happens from a descriptive point of view.

    By the way, why are you are writing this kind of query? What is wrong with :

    SELECT c1 from t1 where c1=1 ????????




    Nathan H.O.
    Moderator
    SQL-Server-Performance.com

Share This Page