InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 14:55:04 GMT20http://www.sqlservercentral.com/Forums/Topic541726-338-1.aspx[quote][b]J Frizzle (12/21/2012)[/b][hr]Can you use EXCEPT ALL like you can with UNION ALL to display even the non-distinct rows?[/quote]I'm pretty sure you cannot. Neither can use use INTERSECT ALL.INTERSECT I'm pretty sure also returns only DISTINCT values.Thus UNION, INTERSECT and EXCEPT all operate in a similar fashion, returning only DISTINCT values.Sun, 23 Dec 2012 22:23:50 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxCan you use EXCEPT ALL like you can with UNION ALL to display even the non-distinct rows?Fri, 21 Dec 2012 07:47:03 GMTJ Frizzlehttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxRegarding using LEFT OUTER JOIN instead of EXCEPT or NOT EXISTS, how do you write such a SQL Statement ? Please use the AdventureWorks demo database to answer the following question: What Vendors do NOT supply Products whose color is Blue or Grey?EXCEPT SQL[code]select *from Purchasing.VendorJOIN (SELECT Purchasing.Vendor.VendorID FROM Purchasing.Vendor EXCEPT (Select Purchasing.ProductVendor.VendorID FROM Production.Product JOIN Purchasing.ProductVendor ON Production.Product.ProductID = Purchasing.ProductVendor.ProductID WHERE Production.Product.color in ( 'Blue','Grey') ) ) as VendorNonColor on VendorNonColor.VendorID = Purchasing.Vendor.VendorID;[/code]NOT EXISTS SQL:[code]select *from Purchasing.Vendorwhere NOT EXISTS (Select 1 FROM Production.Product JOIN Purchasing.ProductVendor ON Production.Product.ProductID = Purchasing.ProductVendor.ProductID WHERE Production.Product.color in ( 'Blue','Grey') AND Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID )[/code]The schema:[code]CREATE TABLE [Purchasing].[Vendor]([VendorID] [int] IDENTITY(1,1) NOT NULL,[Name] varchar(255) NOT NULL , CONSTRAINT [PK_Vendor_VendorID] PRIMARY KEY CLUSTERED ([VendorID] ASC))CREATE TABLE [Production].[Product]([ProductID] [int] IDENTITY(1,1) NOT NULL,[Color] [nvarchar](15) NULL,CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC))CREATE TABLE [Purchasing].[ProductVendor]([ProductID] [int] NOT NULL,[VendorID] [int] NOT NULL,CONSTRAINT [PK_ProductVendor_ProductID_VendorID] PRIMARY KEY CLUSTERED ([ProductID] ASC,[VendorID] ASC),CONSTRAINT [FK_ProductVendor_Product_ProductID] FOREIGN KEY([ProductID]) REFERENCES [Production].[Product] ([ProductID]),CONSTRAINT [FK_ProductVendor_Vendor_VendorID] FOREIGN KEY([VendorID]) REFERENCES [Purchasing].[Vendor] ([VendorID]))GOCREATE NONCLUSTERED INDEX [IX_ProductVendor_VendorID] ON [Purchasing].[ProductVendor] ([VendorID] ASC)go[/code]Mon, 04 Aug 2008 07:58:16 GMTCarl Federlhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxI'd like to add something here... I think a few people have touched on it, but it's pretty important.If you are pulling out a very small amount of records, say, just one or two, then using Except would probably be the better way to go, if you can filter to this level in the except part of your clause.If you are, however, pulling out a rather large set of data, the left outer joins will be the better choice.At least, that's what I generally find with nested queries rather than joined queries.If you can filter down to only a few records early in the game, then nesting is good. Otherwise, it makes your DB take too much of a time hit to query all the records twice, rather than things like hash matches.Mon, 04 Aug 2008 00:07:43 GMTsharon.benderhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxGood point, Carl. Thanks.Sun, 03 Aug 2008 09:28:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxIt is interesting that the result of EXCEPT may be different than a NOT EXISTS. EXCEPT considers NULLS to equal but NOT EXISTS considers NULLS to be unequal. Here is a reproduction:After populating JBMTest per the provide SQL, add some additional rows with null.[code]INSERT INTO dbo.JBMTest (SomeLetters2)SELECT TOP 1000 CHAR(ABS(CHECKSUM(NEWID()))%26+65) + CHAR(ABS(CHECKSUM(NEWID()))%26+65) FROM Master.dbo.SysColumns t1, Master.dbo.SysColumns t2 [/code]EXCEPT returns zero rows:[code]select *from dbo.JBMTestexcept select * from dbo.JBMTest[/code]NOT EXISTS returns all of the rows with NULLs[code]select *from dbo.JBMTest as T1where NOT EXISTS (select 1 from dbo.JBMTest as T2 where T2.RowNum = T1.RowNum and T2.SomeInt = T1.SomeInt and T2.SomeCSV = T1.SomeCSV and T2.SomeMoney = T1.SomeMoney and T2.SomeDate = T1.SomeDate and T2.SomeHex12 = T1.SomeHex12 )[/code]Sat, 02 Aug 2008 15:20:09 GMTCarl Federlhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspx[quote][b]Jeff Moden (8/1/2008)[/b][hr]THAT was a lot of fun! Barry is a heck of a good sport![/quote]Likewise, Jeff!Sat, 02 Aug 2008 10:58:29 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspx[quote][b]Christopher Stobbs (8/1/2008)[/b][hr]Jeff,How would you best two queries to see which is performing better?thanksChris[/quote]One way is already demonstrated in the code I posted previously in this thread. Another way is to set up Profiler for the SPID I'm using to test through and have each code snippet separated from the other using GO.Of course, you can't test something for performance unless you have lot's of data, so I use a "standard" test table for certain things. The code to generate the "standard" test table can be easily modified to suit a wide variety of requirements. The code generator is based on the same principle as the code I use to very quickly generate a Tally table. Here it is...[code]DROP TABLE JBMTestGO--===== Create and populate a 1,000,000 row test table. -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' -- for all rows. -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F) -- Jeff Moden SELECT TOP 1000000 RowNum = IDENTITY(INT,1,1), SomeInt = ABS(CHECKSUM(NEWID()))%50000+1, SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65) + CHAR(ABS(CHECKSUM(NEWID()))%26+65), SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)), SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY), SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME), SomeHex12 = RIGHT(NEWID(),12) INTO dbo.JBMTest FROM Master.dbo.SysColumns t1, Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN--===== A table is not properly formed unless a Primary Key has been assigned -- Takes about 1 second to execute. ALTER TABLE dbo.JBMTest ADD PRIMARY KEY CLUSTERED (RowNum)[/code]Fri, 01 Aug 2008 18:14:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspx[quote][b]rbarryyoung (8/1/2008)[/b][hr][quote][b]jcrawf02 (8/1/2008)[/b][hr]By the way, it's very big of you to contribute to the same thread as Jeff, when he's obviously anti-RBARry, but just can't spell your name . . .:hehe:[/quote]Heh, you don't know the half of it. Check out this post and the thread that follows: [url]http://www.sqlservercentral.com/Forums/FindPost502435.aspx[/url] :D[/quote]THAT was a lot of fun! Barry is a heck of a good sport!Fri, 01 Aug 2008 18:09:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspx[quote][b]jcrawf02 (8/1/2008)[/b][hr]By the way, it's very big of you to contribute to the same thread as Jeff, when he's obviously anti-RBARry, but just can't spell your name . . .:hehe:[/quote]Heh, you don't know the half of it. Check out this post and the thread that follows: [url]http://www.sqlservercentral.com/Forums/FindPost502435.aspx[/url] :DFri, 01 Aug 2008 09:12:16 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxJeff,How would you best two queries to see which is performing better?thanksChrisFri, 01 Aug 2008 08:03:47 GMTChristopher Stobbshttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspx[quote][b]rbarryyoung (7/30/2008)[/b][hr][quote][b]jcrawf02 (7/30/2008)[/b][hr]So, for someone who is using 2000 still, but looking forward to 2005, EXCEPT will let me intersect the two sets, and give me the results that are not overlapping, correct?[/quote]Well technically, no. What you describe is the Exclusive Union, or Disjoint Union (which is the Set equivalent of XOR in logic) which in SQL Server could be expressed as:[code](A UNION B) EXCEPT (A INTERSECT B)[/code]EXCEPT (which used to be called "MINUS" in some old implementations of SQL) is more like a subtraction operator. It returns only the (distinct) elements of the first set that are not in the second set. In logical operators, EXCEPT would be:[code]A AND (NOT B)[/code]Of course that may be what you actually meant, but the details really matter when it comes to logic and set theory descriptions.[/quote]That is what I actually meant, thanks, and I don't know that I really understand what I meant now, so until I do some more reading, I don't think I'll be using it. ;) Thanks for the info!By the way, it's very big of you to contribute to the same thread as Jeff, when he's obviously anti-RBARry, but just can't spell your name . . .:hehe:Fri, 01 Aug 2008 07:28:20 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxdon't forget that except implicitly compares every column, not just keys. so it can be used in situations where a not exists or not in or outer join wouldn't be appropriate or practical. (would you really want to code a join between tables with 120 columns?)Thu, 31 Jul 2008 07:44:20 GMTantonio.collinshttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxOh, yeah... almost forgot... "In the land of the blind, the one eyed man is King!"... That's why most people look at the Execution Plan and believe in it. I don't because I have "two eyes". The Percent of Batch is frequently VERY wrong and should never be considered when trying to optimize code... especially when comparing two methods to select the better performing one. I actually have code that shows that one of two queries that return identical results shows a Percent of Batch as 100% and the other shows 0%... yet, the 100% blows the 0% code away!Ah, but why would you simply take my word for it? (You shouldn't) :P Here's the offending code...[code]SET NOCOUNT ON--=======================================================================================-- Recursive method shown by (Name with-held)--======================================================================================= PRINT '========== Recursive method =========='--===== Turn on some performance counters =============================================== SET STATISTICS IO ON SET STATISTICS TIME ONDECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.--===== Execute the code being tested ===================================================DECLARE @DateVal DATETIME SET @DateVal = '2008-01-01';with mycte as ( select @DateVal AS DateVal union all select DateVal + 1 from mycte where DateVal + 1 < DATEADD(yy, 5, @DateVal) )select @BitBucket = d.datevalfrom mycte dOPTION (MAXRECURSION 0)--===== Turn off the performance counters and print a separator ========================= SET STATISTICS TIME OFF SET STATISTICS IO OFF PRINT REPLICATE('=',90)GO--=======================================================================================-- Tally table method by Jeff Moden--======================================================================================= PRINT '========== Tally table method =========='--===== Turn on some performance counters =============================================== SET STATISTICS IO ON SET STATISTICS TIME ONDECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.--===== Execute the code being tested ===================================================DECLARE @StartDate AS DATETIME SET @StartDate = '2008-01-01'SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,5,@StartDate))) @BitBucket = @StartDate-1+t.N FROM Tally t ORDER BY N--===== Turn off the performance counters and print a separator ========================= SET STATISTICS TIME OFF SET STATISTICS IO OFF PRINT REPLICATE('=',90)[/code]Tally table I used is here...[url]http://www.sqlservercentral.com/articles/TSQL/62867/[/url]Wed, 30 Jul 2008 21:32:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxI may have to write an article about it someday... I've found that WHERE NOT IN beats the OUTER JOIN type of exclusion, WHERE NOT EXISTS correlated subqueries, and a couple of other methods... wouldn't be surprised if it beat the pants off of EXCEPT, as well. :hehe:Wed, 30 Jul 2008 20:50:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspx[quote][b]jcrawf02 (7/30/2008)[/b][hr]So, for someone who is using 2000 still, but looking forward to 2005, EXCEPT will let me intersect the two sets, and give me the results that are not overlapping, correct?[/quote]Well technically, no. What you describe is the Exclusive Union, or Disjoint Union (which is the Set equivalent of XOR in logic) which in SQL Server could be expressed as:[code](A UNION B) EXCEPT (A INTERSECT B)[/code]EXCEPT (which used to be called "MINUS" in some old implementations of SQL) is more like a subtraction operator. It returns only the (distinct) elements of the first set that are not in the second set. In logical operators, EXCEPT would be:[code]A AND (NOT B)[/code]Of course that may be what you actually meant, but the details really matter when it comes to logic and set theory descriptions.Wed, 30 Jul 2008 16:49:13 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspx[quote][b]GSquared (7/30/2008)[/b][hr]I just found something quite interesting on this question.I hadn't realized that BOL says, "EXCEPT returns any distinct values from the left query that are not also found on the right query." Note, "distinct". I hadn't noticed that before.Except will give you the non-overlaps between two queries (two tables, whatever), but it will only return one row for each distinct value.I found this by accident while performing some speed tests on left join vs except. Left join was returning over 8000 rows, while except was giving me 21, because of the way I generated the test data.[/quote]This is very good to know...didn't realize that and can only imagine how frustrating tracking that down could be.Now, I don't have to - thanks. ;)Wed, 30 Jul 2008 15:33:51 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxI just found something quite interesting on this question.I hadn't realized that BOL says, "EXCEPT returns any distinct values from the left query that are not also found on the right query." Note, "distinct". I hadn't noticed that before.Except will give you the non-overlaps between two queries (two tables, whatever), but it will only return one row for each distinct value.I found this by accident while performing some speed tests on left join vs except. Left join was returning over 8000 rows, while except was giving me 21, because of the way I generated the test data.Wed, 30 Jul 2008 15:02:38 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxYes.Wed, 30 Jul 2008 14:48:48 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxSo, for someone who is using 2000 still, but looking forward to 2005, EXCEPT will let me intersect the two sets, and give me the results that are not overlapping, correct?Wed, 30 Jul 2008 10:42:42 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxIn your case LEFT OUTER JOIN is the way to go (and the fastest)but with a lot of columns (and a smallish database) EXCEPT can be powerful consider this applicationtable 1 is a download of a cash register (no more than 4000-1000 items)table 2 is a SQL table supposedly in complete synchronization with said cash register tableEXCEPT will efficiently "pop" the discrepanciesTue, 29 Jul 2008 06:48:48 GMTSeggerman-675349http://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxEXCEPT is not optimized to perform like a join. it's the same as doing thing as you dumping your first query into a temp table and then deleting any rows that match the second query. A properly written join or sub-select could do this more efficiently. Also remember that with EXCEPT both queries have to have the same columns and all columns will be compared.Mon, 28 Jul 2008 06:26:32 GMTantonio.collinshttp://www.sqlservercentral.com/Forums/Topic541726-338-1.aspxHI All,I have just come across the except tool in SQL 2005 and have been running some performance checks to see which is better in the following situation:[code]SELECT id FROM tableAEXCEPT SELECT ClientId FROM tableBSELECT id FROM tableA a LEFT JOIN tableB b ON b.ClientID = a.idWHERE b.id is null[/code]When looking at the execution plan the EXCEPT cost only 6% where the join costs 94%However the time in ms of the EXCEPT is much slower and the LOGICAL reads is much higher.This leaves me confused :-(Could someone let me know or point me in the correct direction of which is better to use and why?ThanksChrisMon, 28 Jul 2008 04:18:41 GMTChristopher Stobbs