Post reply

Unexpected results; SELECT statement with IN and NOT IN

  • September 28, 2011 at 2:41 pm

    #244604

    This is puzzling...at least to me.

    SELECT

    A FROM TABLE 1

    WHERE A NOT IN

    (

    SELECT Field1 FROM B

    )

    AND A IN

    (

    SELECT Field1 FROM C

    )

    The second subquery

    (SELECT Field1 FROM C)

    is invalid. Field1 doesn't exist in table C. Invalid column name Field1. It does not run when run on its own, but when I run the entire query it returns results.

    I'd like to better understand why this happens....

  • September 28, 2011 at 3:13 pm

    #1388121

    It's the same as any other correlated subquery. The processor first checks for the field in the scope of the subquery. If it can't find the field there, it uses the field from the outer query. So, your query is equivalent to

    SELECT

    A FROM

    WHERE A NOT IN

    (

    SELECT Field1 FROM B

    )

    AND A IN

    (

    SELECT

    .Field1 FROM C

    )

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • September 29, 2011 at 4:40 am

    #1388264

    SELECT Field1 FROM C

    The Field1 is Table1's column

    subquery can use outer table's column

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

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