Home Forums SQL Server 2008 T-SQL (SS2K8) Correlated Subquery - Stream Aggregate RE: Correlated Subquery - Stream Aggregate

  • December 19, 2012 at 8:15 am

    #1570107

    ...

    And consider the following selects

    select Col4 from Indexing2

    inner join Indexing

    on Indexing2.Col4 = Indexing.Col2

    where Col4 between '200' and '250'

    select Col4 from Indexing2

    where exists (select * from Indexing where Indexing.Col2 = Indexing2.Col4)

    and Col4 between '200' and '250'

    Essentially, the 2 queries are the same. Why does the 1st query return duplicates and the 2nd query doesn't?

    Also, the the execution plans are identical besides the 2nd query having a stream aggregate. Why is this being added?

    Thanks

    Your two query are essentially very different!

    Having the same filter in the WHERE clause, still your first query returns Col4 from Indexing2 for every matching record from Indexing, but the second query only returns one row from Indexing2, regardless how many matching records found in Indexing.

    If you want query one to behave same way as your query two you need to use DISTINCT in select.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]