Will the following three queries ALWAYS produce exactly the same results?

Question

hi

We want all members who selected category 1 as their favorite category to also have category 9 added as one of their favorite categories. I assume the following three queries will ALWAYS produce exactly the same results ( assuming FavCategory.CategoryID and FavCategory.MemberID form a primary key ):

SELECT 9, md1.MemberId FROM MemberDetails AS MD1 
INNER JOIN FavCategory as FC1
ON MD1.MemberId = FC1.MemberId
WHERE FC1.CategoryId = 1 
      AND NOT EXISTS
             (SELECT * FROM FavCategory AS FC2
              WHERE FC2.MemberId = FC1.MemberId 
                    AND FC2.CategoryId = 9);


SELECT 9, MemberId FROM MemberDetails AS MD1
WHERE EXISTS
      (SELECT * FROM FavCategory FC1
       WHERE FC1.CategoryId = 1 AND FC1.MemberId = MD1.MemberId
             AND NOT EXISTS
                     (SELECT * FROM FavCategory AS FC2
                      WHERE FC2.MemberId = FC1.MemberId 
                            AND FC2.CategoryId = 9));


SELECT 9, MemberId FROM MemberDetails AS MD1
WHERE EXISTS
     (SELECT * FROM FavCategory FC1
      WHERE FC1.CategoryId = 1 AND FC1.MemberId = MD1.MemberId)
            AND NOT EXISTS
                     (SELECT * FROM FavCategory AS FC2
                      WHERE MD1.MemberId = FC2.MemberId 
                            AND FC2.CategoryId = 9);

thanx

Answer 1

Since there is no order by you are not guaranteed the same ordering. I know, probably not what you were asking.

The first query returns the set of MemberDetails records that have a corresponding entry in FavCategory via inner join on MemberID.

The next two queries return the set of MemberDetails records that have a corresponding entry in FavCategory via an exists clause based on MemberID - kind of a manual inner join. Of these two, the not exists clause varies by comparing to MemberID in either MemberDetails or FavCategory - but there should be no difference as MemberID is the same.

So I believe it is safe to say that the unordered set of data resulting from each of the three queries will be the same.

That said, you should probably test. Visual Studio (2010 and some varieties of 2005/2008) has a tool that can populate tables with tons of arbitrary data - this might be a good way to verify the musings of some random fool on the Internet. :)

Answer 2

I also believe all three will provide exactly the same results, but I'm not 100% sure the database will like the fact that you have not prefixed MemberId with an alias, since there are multiple columns by that name (but it might as they all have the same value).

BUT, why do you need to involve the MemberDetails table. To me it appears you only need its ID, which you also have as FavCategory.MemberId so I believe you can make them all a bit smarter (only involving FavCategory, twice).