September 3, 2013 at 12:08 pm
It's come to my attention, from taking SQL Server 2012 practice tests, that I don't know how to combine SET operators UNION, INTERSECT, AND EXCEPT for situations requiring more than one type to solve a problem.
I would like to understand before it turns into real life.
Practice question:
The Hovercraft database has three tables related to Hovercraft design, OldDesign, CurrentDesign, and FutureDesign. Some designs are present in more than one table. You want to generate a list of all items in the FutureDesign table as well as FutureDesign items that are present in both the OldDesign and CurrentDesign tables.
Answer:
SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign INTERSECT SELECT HovercraftModelID, Name FROM Hovercraft.CurrentDesign UNION SELECT HovercraftModelID, Name FROM Hovercraft.FutureDesign
In the case of my DDL, it's the color red, plus black and white, that should be returned....and it does that using same semantics.
create table oldColors
(color varchar(20));
insert into oldColors
values
('red'),
('pink'),
('pale blue'),
('light yellow');
create table currentColors
(color varchar(20));
insert into currentColors
values
('red'),
('blue'),
('salycilic yellow');
create table futureColors
(color varchar(20));
insert into futureColors
values
('black'),
('white'),
('red');
SELECT color
FROM oldColors
INTERSECT
SELECT color
FROM currentColors
UNION
SELECT color
FROM futureColors;
My confusion:
But, to me the correct answer reads like its going to retrieve only the designs that OldDesign has in common with CurrentDesign and all the designs in FutureDesign. But when I run the set operation it does what is asked.
Is this a situation of 'White Panda Eats Shoots and Leaves'? which could be interpreted differently depending on how you read it IE. that I'm just reading the set operation with the wrong mindset?
Can anyone relate to my confusion and help me out of it?
--Quote me
September 3, 2013 at 4:12 pm
It seems to me that the confusion is really with:
Some designs are present in more than one table. You want to generate a list of all items in the FutureDesign table as well as FutureDesign items that are present in both the OldDesign and CurrentDesign tables.
and
But, to me the correct answer reads like its going to retrieve only the designs that OldDesign has in common with CurrentDesign and all the designs in FutureDesign.
But this is really the same as above, but a more straightforward to say it.
In any case, note that what happens if you run:
SELECT color
FROM oldColors
UNION
SELECT color
FROM currentColors
INTERSECT
SELECT color
FROM futureColors;
Note that this query does not first compute the union of old and current colours and then intersect that with the future colours. UNION is akin to addition and INTERSECT to multiplication, so INTERSECT has higher precedence. You can change that with parentheses:
( SELECT color
FROM oldColors
UNION
SELECT color
FROM currentColors)
INTERSECT
SELECT color
FROM futureColors;
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 3, 2013 at 10:45 pm
That's very appreciated. You saw the mistake in my own set operation which should have been (and I thought it was) just like the practice test query.
SELECT color
FROM oldColors
INTERSECT SELECT color
FROM currentColors
UNION SELECT color
FROM futureColors;
and gave me key to understanding: precedence.
I'll know to look up precedence whenever unsure about the combination of set operators.
Thanks very much, Erland.
--Quote me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply