Introduction
UNION
INTERSECT
9. How INTERSECT works
EXCEPT

Instruction

Excellent! Here is another keyword: INTERSECT. Let's change our example a little bit:

SELECT
  Year
FROM Cycling
WHERE Country = N'Germany'

INTERSECT

SELECT
  Year
FROM Skating
WHERE Country = N'Germany';

Instead of UNION (or UNION ALL), we've put INTERSECT in there. What's the difference?

Well, UNION gave you all the results from the first query plus the results from the second query. INTERSECT, on the other hand, only shows the rows that belong to both tables.

In this case, we would get the years when Germany got a medal in cycling and speed skating at the same time.

The conditions here stay the same: the number of columns in both tables must be the same, and the data types of the columns must match.

Exercise

Find the names of each person who has medals both in cycling and in skating.

Stuck? Here's a hint!

Type:

SELECT
  Person
FROM Cycling
INTERSECT
SELECT
  Person
FROM Skating;