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 «««56789»»»

Intersect, Except, Union, All and Any Expand / Collapse
Author
Message
Posted Friday, May 21, 2010 10:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 17,807, Visits: 15,728
Nice article. Thanks for sharing.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #926083
Posted Friday, May 21, 2010 10:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 6:49 AM
Points: 5, Visits: 80
traughberk (5/21/2010)
I have a question about LEFT JOIN example near the beginning where he starts talking about EXCEPT where he wrote the following SQL.

SELECT C.CustomerID
FROM Sales.Customer AS C
LEFT JOIN Sales.SalesOrderHeader AS OH
ON C.CustomerID = OH.CustomerID
AND OrderDate>='2004-07-01'
WHERE OH.CustomerID IS NULL
AND C.TerritoryID=10


Can someone please explain the "AND OrderDate>='2004-07-01' " in the FROM clause? I've never seen that before.

Thanks.
Kris


Kris - the "AND OrderDate >= '2004-07-01'" is filtering under the JOIN function. When you JOIN two tables you define how the two relate to each other. In the example, the "C" table links to the "OH" table through the CustomerID field in each table being equal. You could state that they be different, in which case you link each record in table "C" to all other recrods in table "OH" where the CustomerID's are different. Why you would want to do this is beyond me. I am just illustrating some functionality. So the second parameter in the JOIN linking definition is that on top of the fact that the CustomerID's in table "C" and table "OH" have to be the same, the Order Date must also be greater than or equal to July 1st, 2004. An alternative is to drop the AND statement from the JOIN function, and apply it in the WHERE clause. One way may be more efficient than the other.
Post #926096
Posted Friday, May 21, 2010 10:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:16 PM
Points: 22, Visits: 181
Never mind folks. I figured it out myself which is what I should have done in the first place. I guess I'm just lazy this morning. Sorry for the wasted post.
Post #926109
Posted Friday, May 21, 2010 12:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 15, 2014 7:09 AM
Points: 302, Visits: 258
Thank you for a great article, Dave. I never really thought of using any of these commands as I normally would place data into temp tables to run JOIN queries that produce similar results, and I never fully understood their use until seeing it graphically in your article. I can now do a:

SELECT  no_match_id   FROM  [production].[dbo].[ytd_table]
EXCEPT
SELECT no_match_id FROM [production].[dbo].[current_month_table]

To get a list of ID's in my main table that are no longer in the new month's data I load. There's more complexity to it than what I wrote here, but the EXCEPT command will achieve the same results for my table updates than the lengthy queries I'm using now.

And I'm sure I can find many other uses for utilizing both INTERSECT and EXCEPT to replace and simplify my current methodologies in which I use JOINs to match on as many as 60+ columns.

Thanks again for a great article!


Danny Sheridan
Comtekh, Inc.
Post #926198
Posted Friday, May 21, 2010 2:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 22, 2012 1:02 PM
Points: 22, Visits: 45
I think a good use of INTERSECT and EXCEPT is when you need to compare many fields. It is much quicker than writing a join or where exists on 5 columns.
Post #926272
Posted Friday, May 21, 2010 2:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
The reason for the AND OrderDate > '2004-07-01' in the JOIN Clause is so that ALL Customers get returned by the left join. If you moved it to the WHERE Clause then any customers who had not had an order after that date would not show up in the result set.
--
JimFive
Post #926282
Posted Friday, May 21, 2010 2:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:16 PM
Points: 22, Visits: 181
Robert Dudley (5/21/2010)
traughberk (5/21/2010)
I have a question about LEFT JOIN example near the beginning where he starts talking about EXCEPT where he wrote the following SQL.

SELECT C.CustomerID
FROM Sales.Customer AS C
LEFT JOIN Sales.SalesOrderHeader AS OH
ON C.CustomerID = OH.CustomerID
AND OrderDate>='2004-07-01'
WHERE OH.CustomerID IS NULL
AND C.TerritoryID=10


Can someone please explain the "AND OrderDate>='2004-07-01' " in the FROM clause? I've never seen that before.

Thanks.
Kris


Kris - the "AND OrderDate >= '2004-07-01'" is filtering under the JOIN function. When you JOIN two tables you define how the two relate to each other. In the example, the "C" table links to the "OH" table through the CustomerID field in each table being equal. You could state that they be different, in which case you link each record in table "C" to all other recrods in table "OH" where the CustomerID's are different. Why you would want to do this is beyond me. I am just illustrating some functionality. So the second parameter in the JOIN linking definition is that on top of the fact that the CustomerID's in table "C" and table "OH" have to be the same, the Order Date must also be greater than or equal to July 1st, 2004. An alternative is to drop the AND statement from the JOIN function, and apply it in the WHERE clause. One way may be more efficient than the other.


Thanks for the reply. I played around with it for a bit and it seems that it does make a difference where the date filter is placed. If it is placed in the FROM clause you are basically joining table C to a subset of table OH. If you put it in the WHERE clause, you are joining table C to all of table OH. It's similar to the example he wrote following that using the IN statement. (Obviously its similar. That's why he wrote it.)
Post #926287
Posted Friday, May 21, 2010 2:54 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
The other, and maybe the main, reason for putting conditionals into the join is to make the query more efficient. If you blind join a table then all matching rows get included in intermediate results (within the server) only to be filtered out later on during the processing of the WHERE clause. I never understood that until I read some very excellent articles on how JOINS work right here on SQLServerCentral.com We were working on a query that just sucked and often timed out. We joined millions of transactions to thousands of customers only to filer that down to hundreds of results. Moving the conditional into the join then joins only those few hundred transactions to the customer set. One heck of performance boost. Check them out.

ATB

Charles Kincaid

Post #926291
Posted Friday, May 21, 2010 3:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:16 PM
Points: 22, Visits: 181
Thanks for the replies everyone. I've learned something new today. That alone makes it a good day.
Post #926312
Posted Sunday, May 23, 2010 6:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:51 PM
Points: 30, Visits: 119
I'm sure I once read, here, that WHERE IN tends to be expensive. Your test results challenge this assertion. I wonder if SQL Server 2008 is optimising the execution plan a lot more than it used to.
Post #926579
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse