May 13, 2015 at 3:06 pm
i created a CTE which finds a subset of records from a table
I then ran a SELECT statement against the same table as
SELECT * FROM TABLE
EXCEPT (SELECT * FROM CTE)
Is it possible to add another EXCEPT statement after the CTE EXCEPT statement to cover
a condition not incorporated in the CTE definition?
May 13, 2015 at 3:14 pm
It's possible, but you need to be sure that you're applying the correct precedence.
If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:
Expressions in parentheses
The INTERSECT operator
EXCEPT and UNION evaluated from left to right based on their position in the expression
May 17, 2015 at 12:45 pm
For fun and furthering on Luis's answer, this examples brings back all primes between 10 and 100 using except.
😎
USE Test;
GO
SET NOCOUNT ON;
/*
For fun only, selecting all primes between ten and one hundred
using except.
*/
DECLARE @SAMPLE_SIZE INT = 100;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,EVERY_OTHER(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 2 = 0
)
,EVERY_THIRD(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 3 = 0
)
,EVERY_FOURTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 4 = 0
)
,EVERY_FIFTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 5 = 0
)
,EVERY_SIXTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 6 = 0
)
,EVERY_SEVENTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 7 = 0
)
,EVERY_EIGHTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 8 = 0
)
,EVERY_NINETH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 9 = 0
)
SELECT
NM.N AS TRAN_ID
FROM NUMS NM
WHERE NM.N > 10
EXCEPT
(SELECT * FROM EVERY_OTHER)
EXCEPT
(SELECT * FROM EVERY_THIRD)
EXCEPT
(SELECT * FROM EVERY_FOURTH)
EXCEPT
(SELECT * FROM EVERY_FIFTH)
EXCEPT
(SELECT * FROM EVERY_SIXTH)
EXCEPT
(SELECT * FROM EVERY_SEVENTH)
EXCEPT
(SELECT * FROM EVERY_EIGHTH)
EXCEPT
(SELECT * FROM EVERY_NINETH)
;
Output
TRAN_ID
11
13
17
19
23
29
31
37
41
43
47
53
59
61
67
71
73
79
83
89
97
May 17, 2015 at 4:26 pm
I'm amazed at this question for 2 reasons...
1) The coverage for such a question in Books Online is not obvious. You actually have to infer that such a capability exists by reading the remarks. There is no example of using more than two sets of data and even the syntax example doesn't show that such a thing is possible.
2) That, not withstanding, I don't understand why people post such questions instead of just trying it. It takes longer to post a question on this subject than it does to write code to try it.
--Jeff Moden
May 17, 2015 at 5:49 pm
Jeff Moden (5/17/2015)
I don't understand why people post such questions instead of just trying it.
That's what I would do.
Of course, then Eirikur would have no fun posting up an example using prime numbers (very entertaining).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2015 at 6:59 pm
Eirikur Eiriksson (5/17/2015)
For fun and furthering on Luis's answer, this examples brings back all primes between 10 and 100 using except.😎
USE Test;GO
SET NOCOUNT ON;
/*
For fun only, selecting all primes between ten and one hundred
using except.
*/
DECLARE @SAMPLE_SIZE INT = 100;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,EVERY_OTHER(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 2 = 0
)
,EVERY_THIRD(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 3 = 0
)
,EVERY_FOURTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 4 = 0
)
,EVERY_FIFTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 5 = 0
)
,EVERY_SIXTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 6 = 0
)
,EVERY_SEVENTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 7 = 0
)
,EVERY_EIGHTH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 8 = 0
)
,EVERY_NINETH(N) AS
(
SELECT
NM.N
FROM NUMS NM
WHERE NM.N % 9 = 0
)
SELECT
NM.N AS TRAN_ID
FROM NUMS NM
WHERE NM.N > 10
EXCEPT
(SELECT * FROM EVERY_OTHER)
EXCEPT
(SELECT * FROM EVERY_THIRD)
EXCEPT
(SELECT * FROM EVERY_FOURTH)
EXCEPT
(SELECT * FROM EVERY_FIFTH)
EXCEPT
(SELECT * FROM EVERY_SIXTH)
EXCEPT
(SELECT * FROM EVERY_SEVENTH)
EXCEPT
(SELECT * FROM EVERY_EIGHTH)
EXCEPT
(SELECT * FROM EVERY_NINETH)
;
Output
TRAN_ID11
13
17
19
23
29
31
37
41
43
47
53
59
61
67
71
73
79
83
89
97
No need to account for multiples of values divisible by a lower group. For this example all you need to exclude are evens (/ 2), thirds (/ 3), fifths (/ 5), and sevenths (/ 7).
May 17, 2015 at 8:10 pm
Jeff Moden (5/17/2015)
I'm amazed at this question for 2 reasons...1) The coverage for such a question in Books Online is not obvious. You actually have to infer that such a capability exists by reading the remarks. There is no example of using more than two sets of data and even the syntax example doesn't show that such a thing is possible.
2) That, not withstanding, I don't understand why people post such questions instead of just trying it. It takes longer to post a question on this subject than it does to write code to try it.
I certainly agree with 2. But then I disagree with 1, I think Books Online is clear, so 2 for me amounts to checking that books online isn't telling me something wrong.
The syntax definition
{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }
is pretty straightfiorward - it specifies how EXCEPT or INTERSECT is used in a query, so quite clearly what it defines is either a query_specification or a query_expression, so whichever it is the bit before the EXCEPT | INTERSECT shown can contain EXCEPT | INTERSECT, and so can the bit after. So chain together as many as you like, but do note that the nesting is ambiguous unless you want flat left to right so use brackets if you don't (that bit is only clear if you read the additional text, the syntax definition doesn't constrain it).
Tom
May 18, 2015 at 10:30 am
Just to point out an alternative, instead of using multiple EXCEPTs, each with a SELECT pulling a set you'd like to exclude, you could also just UNION the SELECTs in one EXCEPT. Interestingly, at least in a couple quick tests I threw together, I get slightly different plans with slightly different performance characteristics for each.
Cheers!
May 18, 2015 at 11:09 am
dwain.c (5/17/2015)
Jeff Moden (5/17/2015)
I don't understand why people post such questions instead of just trying it.That's what I would do.
Of course, then Eirikur would have no fun posting up an example using prime numbers (very entertaining).
Spot on there Dwain, I think I have somewhat of a set based humor:-D and as the "primer" shows, now with a mixture of the English sarcasm;-)
😎
May 18, 2015 at 6:29 pm
Jacob Wilkins (5/18/2015)
Just to point out an alternative, instead of using multiple EXCEPTs, each with a SELECT pulling a set you'd like to exclude, you could also just UNION the SELECTs in one EXCEPT. Interestingly, at least in a couple quick tests I threw together, I get slightly different plans with slightly different performance characteristics for each.Cheers!
Yes, but sometimes an EXCEPT is there to indicate things you don't want to exclude rather that to pull a set you want to exclude, and then teh multiple EXCEPTs cant'be reduced to just one by USING UNION.
SELECT X from A EXCEPT (SELECT X from B EXCEPT SELECT X from C)
is a nice simple example of that.
Tom
May 18, 2015 at 8:47 pm
@tom: Absolutely, but from the OP's post, it seemed he was wanting a way to exclude another set in addition to the set returned by the CTE, in which case my point holds.
Of course if he's wanting to nest EXCEPTs, as in your example, instead of simply using them to indicate additional excluded sets, as in his post and Eirikur's prime numbers example, then everything changes 🙂
May 18, 2015 at 9:09 pm
In Ben-Gan's T-SQL Fundamentals 2012 (Ch. 6) he dedicates a whole chapter to set operators. I would encourage anyone to trying to get a grasp on this topic to take a look at that as he does a much better job explaining them, precedence, how to manipulate set operator precedence, etc than BOL IMHO. He includes some great examples and explains things in a way that I have never seen set operators explained before (e.g. referring to UNION as UNION DISTINCT.) That's my 2 cents.
-- Itzik Ben-Gan 2001
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply