July 20, 2009 at 8:22 am
Images have been loaded.
July 20, 2009 at 8:47 am
I learnt about INTERSECT when I had a query which I couldn't write any other way.
http://sci.esa.int/science-e/www/object/index.cfm?fobjectid=30910
you see there are missions and topics, and an option of Match Any (or) of those selected and Match All (and).
Unfortunately Missions and Topics all come from the same attribute id column, so I needed to compare missions and topics seperately, before 'AND'ing them together :).
A logical nightmare.... and I was considering looping queries until .. INTERSECT
the code...
INTERSECT
(SELECT objectid FROM OC4_OBJECTATTRIBUTEMAP where attributeid = #i#)
INTERSECT (SELECT objectid FROM OC4_OBJECTATTRIBUTEMAP where attributeid IN (#m#))
INTERSECT
(SELECT objectid FROM OC4_OBJECTATTRIBUTEMAP where attributeid = #i#)
INTERSECT (SELECT objectid FROM OC4_OBJECTATTRIBUTEMAP where attributeid IN (#t#))
code is a bit broken, but you get the idea!
July 20, 2009 at 9:31 am
Very nice article. One clarrification, INTERSECT and EXCEPT are available in SQL 2005 (article refers only to 2008). I use these quite a bit in my 2005 env, especially for adhoc table comparisons.
July 20, 2009 at 10:11 am
Even though I still use my left and inner joins...
I think EXCEPT & Intersect are very nice. It's so much easier to explain data to some one who is not so much knowledgeable about joins which can be very initimidating at first.
Just my 0.2c!
--
:hehe:
July 20, 2009 at 10:32 am
Jeff Moden (7/19/2009)
Why would anyone think that INTERSECT is easier than INNER JOIN?
How about finding all records matching records between two tables with identical structure? Would you rather write a query joining two tables on 63 columns (for example) with the possibility of NULL values in some of the columns, or would you rather write a simple INTERSECT query? Personally, I'll go with the simplier INTERSECT.
July 20, 2009 at 11:02 am
Great article David!
David mentions that he discovered these in training materials for SQL 2008. Were INTERSECT and EXCEPT not introduced in SQL 2005 and as such, just as valid in that version?
July 20, 2009 at 11:07 am
Scott Jacobson (7/20/2009)
Great article David!David mentions that he discovered these in training materials for SQL 2008. Were INTERSECT and EXCEPT not introduced in SQL 2005 and as such, just as valid in that version?
Yes, sir. Except and Intersect have been available since SQL 2005.
July 20, 2009 at 11:17 am
I really appreciate this article. I often see that when new features come out for SQL Server, Microsoft advertises them so much, as if they were a godsend.
I often see that using techniques like these make coding more complicated where simpler queries will do the trick for less cost.
July 20, 2009 at 11:24 am
Adam Haines (7/20/2009)
Yes, sir. Except and Intersect have been available since SQL 2005.
My mistake, I didn't see the post on page 2 that already mentioned this.
July 20, 2009 at 11:46 am
Thanks for the post. It's exactly what I needed for the problem I'd been working on. The 'except' is what I was doing using the "not in" approach. When the table in the "not In" clause was very large, it took hours in SQL2005 and was not finishing (wasn't a problem in sql2000). I changed it to the "left join" approach and it took 21 seconds. So I'm guessing the 3 different approaches may take about the same amount of time unless the tables have a lot of data.
July 20, 2009 at 11:49 am
Well researched, well proven and well written!
You have further proven that Microsoft has hired too many people who justify their jobs by creating duplicate crap that does the work that ANSI SQL does. No different from the support documents that do not have fit the problems they describe.
In the case of T-SQL, how much faster would our queries run if the engine didn't have this (and other) crap in it? We will likely never know. The monkeys that wrote the pointless additions likely got raises, ski trips and some additional stock before becoming product managers. They will use the same "reasoning" to add "features" to the next version. Run for your lives!
July 20, 2009 at 12:26 pm
Why would anyone think that INTERSECT is easier than INNER JOIN?
As mentioned above, for ad-hoc table comparison, these commands are indispensable and very easy. I setup my query like this:
SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2
EXCEPT
SELECT * FROM Table1
To quickly see the differences, I just highlight the first three rows and execute, and then highlight the last three rows and execute. This is tons easier than setting up the necessary joins when all you want to know is what are the differences (or what are the same records).
July 20, 2009 at 4:45 pm
Great article David. Nice work.
ATBCharles Kincaid
July 20, 2009 at 5:06 pm
I have had the same problem since I started visiting this site. Yesterday I decided to do something about it and googled for an answer. I tried several things re Internet Explorer options in response to the hits I got, all to no avail.
Today I re-visited the article and guess what? Now I get the pictures. I didn't even restart my PC.
July 20, 2009 at 6:57 pm
Thanks for the Article Dave,
I would have thought you might have seen some performance benefits using those new commands had you executed against a VLDB applications, perhaps as large as few TBs. I would be entirely wrong, as this is merely a surmise and conjecture but Adventure Works database, although quite complete a model, lacks in volume for obvious reasons. Personally, I did not have a chance to work on such a humongous database in my career so I cannot really comment on this.
Thanks,
DP
Viewing 15 posts - 16 through 30 (of 86 total)
You must be logged in to reply to this topic. Login to reply