Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Intersect, Except, Union, All and Any Expand / Collapse
Author
Message
Posted Monday, July 20, 2009 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:26 AM
Points: 5, Visits: 122
Hello, the article appears very interesting and useful, but I am concerned that I am missing important parts of it because the image links are broken.

Will you fix these links so we can see the entire article as you intended?

Thanks!
Post #755693
Posted Monday, July 20, 2009 7:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 12:06 PM
Points: 2, Visits: 66
Images would be extremely helpful.
Post #755740
Posted Monday, July 20, 2009 7:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:02 AM
Points: 2,278, Visits: 3,065
Jeff Moden (7/19/2009)
Why would anyone think that INTERSECT is easier than INNER JOIN?


Jeff,

In some cases intersect is easier to use than inner join, if you want to compare resultsets. For example, an inner join requires you to add EVERY column to the inner join list or to the predicate to achieve what you could accomplish by use the intersect operator. I am not saying this is the best method, but it can sometimes reduce coding and save you time. Look at the example below and imagine if you wanted to compare tables with 15+ columns. Essentially with intersect, you can copy and paste the same query on both sides of the operator.

Note: To those reading this, you should not use select * in your select list. I did so below to demonstrate a point.

E.g.
DECLARE @t1 TABLE(
id INT,
col1 CHAR(1),
col2 SMALLINT,
col3 CHAR(1),
col4 CHAR(1),
col5 CHAR(1)
)

INSERT INTO @t1 VALUES (1,'a',10,'z','z','z');
INSERT INTO @t1 VALUES (2,'b',20,'z','z','z');
INSERT INTO @t1 VALUES (3,'c',30,'z','z','z');
INSERT INTO @t1 VALUES (4,'d',40,'z','z','z');

DECLARE @t2 TABLE(
id INT,
col CHAR(1),
col2 SMALLINT,
col3 CHAR(1),
col4 CHAR(1),
col5 CHAR(1)
)

INSERT INTO @t2 VALUES (1,'a',10,'z','z','z');
INSERT INTO @t2 VALUES (2,'b',20,'z','z','z');
INSERT INTO @t2 VALUES (4,'d',30,'z','z','z');
INSERT INTO @t2 VALUES (5,'e',40,'z','z','z');

--intersect
SELECT *
FROM @t1

INTERSECT

SELECT *
FROM @t2

--equivilant
SELECT
t1.*
FROM @t1 t1
INNER JOIN @t2 t2
ON t1.id = t2.id
AND t1.col1 = t2.col
AND t1.col2 = t2.col2
AND t1.col3 = t2.col3
AND t1.col4 = t2.col4
AND t1.col5 = t2.col5

As for except, I find this operator quite useful when trying to diagnose RI violaters and resultsets that dont match between source and destination. Except has the same benefits as Intersect, which is namely reduced typing.

I personally dont allow or use these operators in production code, but I sometimes use these operators to quickly retrieve information.

I recently blogged about intersect and except. You can have a look if you like:

http://jahaines.blogspot.com/2009/07/should-i-intersect-or-except.html




My blog: http://jahaines.blogspot.com
Post #755770
Posted Monday, July 20, 2009 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 29, 2009 11:38 AM
Points: 2, Visits: 17
I am not near my workstation to test, but I am curious if the tests were affected by caching? Any time I have two statements that are different but produce the same timing I am suspicious. I know that these statements may actually run the same in the engine, using the same query plan, but then again the difference might be in the details there.

Perhaps there is a difference in how the query performs when there is no data already in cache. I know that this has me interested enough that I want to test this against a massive dataset, then retest with the old query after cycling SQL.

Great article though, this may prove very interesting.
Post #755789
Posted Monday, July 20, 2009 8:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
Lee Hilton (7/20/2009)
I am not near my workstation to test, but I am curious if the tests were affected by caching? Any time I have two statements that are different but produce the same timing I am suspicious. I know that these statements may actually run the same in the engine, using the same query plan, but then again the difference might be in the details there.

Perhaps there is a difference in how the query performs when there is no data already in cache. I know that this has me interested enough that I want to test this against a massive dataset, then retest with the old query after cycling SQL.

Great article though, this may prove very interesting.


I know that the author is using the AdventureWorks database, but I'm curious just how much data he was testing on.


Random Technical Stuff
Post #755805
Posted Monday, July 20, 2009 8:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,284, Visits: 15,748
Images have been loaded.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #755806
Posted Monday, July 20, 2009 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 10, 2010 2:15 AM
Points: 3, Visits: 8
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...

<!--- missions query --->
<cfif isdefined ("m") and comparenocase(m,"")>
<cfif mission_operand EQ 'Match All (AND)'>
<cfloop index="i" list="#m#" delimiters=",">
INTERSECT
(SELECT objectid FROM OC4_OBJECTATTRIBUTEMAP where attributeid = #i#)
</cfloop>
<cfelseif mission_operand EQ 'Match Any (OR)'>
INTERSECT (SELECT objectid FROM OC4_OBJECTATTRIBUTEMAP where attributeid IN (#m#))
</cfif>
</cfif>

<!--- topics query --->
<cfif isdefined ("t") and comparenocase(t,"")>
<cfif topic_operand EQ 'Match All (AND)'>
<cfloop index="i" list="#t#" delimiters=",">
INTERSECT
(SELECT objectid FROM OC4_OBJECTATTRIBUTEMAP where attributeid = #i#)
</cfloop>
<cfelseif topic_operand EQ 'Match Any (OR)'>
INTERSECT (SELECT objectid FROM OC4_OBJECTATTRIBUTEMAP where attributeid IN (#t#))
</cfif>
</cfif>

code is a bit broken, but you get the idea!
Post #755846
Posted Monday, July 20, 2009 9:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 8, 2012 12:30 AM
Points: 150, Visits: 3,892
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.
Post #755919
Posted Monday, July 20, 2009 10:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
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!


--
Post #755966
Posted Monday, July 20, 2009 10:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 20,807, Visits: 32,745
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #755987
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse