I’d heard tell of the “EXCEPT clause,” but this is my first time seeing it. Isn’t it beautiful? I have wished before for such a function, but I didn’t expect it to look so clean on the page. In Access, I have jumped through a few hoops to achieve a result similar to what this appears to do.
The INTERSECT Clause sounds pretty cool too. I think the equivalent of this definition (at least as far as data returned) in Access would be making inner joins to the fields you want and only pulling those fields down into the bottom.
^ Here’s my attempt to represent my understanding of the INTERSECT if represented in Microsoft Access. But here’s the rub. If I were to write a SQL query for the above Access query, I think I would write it as follows:
This is a very different-looking construct. I went ahead and ran it both ways in SQL and got very different results. For the results of the query using the inner join, I got 72591 rows, and in the results using the INTERSECT query from the Microsoft training, I got a mere 238 rows. Looking closer, I notice that my INTERSECT query doesn’t have immediately obvious repeated values, and my Inner Join query does. I go back and add a “Group By” clause, and then my results match. In these queries using the AdventureWorks tables, both queries ran very quickly. I do not know how the dark underbelly of SQL Server may treat these two queries, so I don’t know if they are equivalent in CPU usage or just in data returned. Here are the two queries with the equivalent results, if anybody else is looking to play:
And just to round out this post, I did go back into Microsoft Access, add the “Group By” and found 238 rows that way as well:
All things being equal, I love the idea of the INTERSECT query.