SQLSACT (12/19/2012)
Why does the 1st query return duplicates and the 2nd query doesn't?
... the 2nd query having a stream aggregate. Why is this being added?
Thanks
The first query does a JOIN; joins return all matching rows, whether duplicate matches or not.
In the second query, you used EXISTS() rather than join. EXISTS() just verifies whether or not a row exists. Since SQL has chosen to use a JOIN to generate the final results, SQL uses the stream aggregate to eliminate duplicate join values, thus insuring that only a single match occurs for each join to the other table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.