UNION, UNION ALL, EXCEPT, INTERSECT

Question 1

How does UNION work?

Answer 1

It returns all rows from the two queries, discarding duplicates.

SQL Server 70-461 04-02b to 04-03

Question 2

What is the general format of two queries using UNION?

Answer 2

<qry1 Select Statement>
UNION
<qry2 Select Statement>

Just making a sentence long enough so the code will left justify for this flash card.

SQL Server 70-461 04-02b to 04-03

Question 3

How is UNION ALL different from UNION?

Answer 3

It doesn’t elminate duplicates

SQL Server 70-461 04-02b to 04-03

Question 4

If UNION and UNION ALL will return the same results, i.e. there are no duplicates, which should you use?

Answer 4

UNION ALL. UNION may attempt to eliminate duplicates, even though there aren’t any, and it could cause a performance issue.

SQL Server 70-461 04-02b to 04-03

Question 5

How does INTERSECT work?

Answer 5

It returns only distinct rows that are common to both sets. If a row appears at least one time in the first set and one time in the second set it will be included one time in the result of the INTERSECT.

SQL Server 70-461 04-02b to 04-03

Question 6

What is the general form of two queries using INTERSECT?

Answer 6

<qry1 Select Statement>
INTERSECT
<qry2 Select Statement>

Just making a sentence long enough so the code will left justify for this flash card.

SQL Server 70-461 04-02b to 04-03

Question 7

What is the general form of two queries using EXCEPT?

Answer 7

<qry1 Select Statement>
EXCEPT
<qry2 Select Statement>

Just making a sentence long enough so the code will left justify for this flash card.

SQL Server 70-461 04-02b to 04-03

Question 8

How would <qry1> UNION <qry2> INTERSECT <qry3> be evaluated?

Answer 8

• The intersection between qry2 and qry3 happens first.
• Next, a union between the result of the intersection and qry1 takes place.

SQL Server 70-461 04-02b to 04-03

Question 9

What can you do to force precendence the way you want it to be for joins involving more than one set operator?

Answer 9

• Use Parentheses
• From previous example:
  (<qry1> UNION <qry2>)
  INTERSECT <qry3>

SQL Server 70-461 04-02b to 04-03

Question 10

What are the three types of set operators?

Answer 10

1. UNION
2. INTERSECT
3. EXCEPT

SQL Server 70-461 04-02b to 04-03

Question 11

What is the multi-set operator?

Answer 11

UNION ALL

SQL Server 70-461 04-02b to 04-03

Question 12

How does EXCEPT work?

Answer 12

• It returns distinct rows that appear in the first query but not in the second.
• If a row occurs at least once in the first query and zero times in the second, it’s returned once in the output.

SQL Server 70-461 04-02b to 04-03

Question 13

What four guidelines must you follow when working with set operators?

Answer 13

1. Number of columns has to be the same and column types of corresponding columns must be compatable.
2. The individual queries can’t use ORDER BY but the result of the set operator can.
3. Column names determined by first query.
4. Set operators consider two NULLS equal.

SQL Server 70-461 04-02b to 04-03

Question 14

What is the order of precedence for set operators?

Answer 14

• Intersect precedes UNION and EXCEPT
• UNION and EXCEPT are evaluated from left to right based on their position in the expression.

SQL Server 70-461 04-02b to 04-03