22 May

SET Operators: Intersect

INTERSECT, is essentially the reverse of EXCEPT. What it does is return unique records that match in the two tables that intersect. For example,

SELECT name, sex, age
FROM junk
INTERSECT
SELECT name, sex, age
FROM allMale

name	        sex	age
Cameron, L	M	47
Derber, B	M	25
Jones, M	M	29
King, E	        M	35
LaMance, K	M	51
Murray, W	M	27
Peterson, V	M	30
Pitts, D	M	34
Underwood, K	M	60
Warren, C	M	54

In reality it’s simply an inner join. In fact, that’s one way we would write this query in MySQL.

mysql> SELECT j.name, j.sex, j.age
    -> FROM junk j
    -> INNER JOIN allMale a
    -> USING (name, sex, age);
+--------------+------+------+
| name         | sex  | age  |
+--------------+------+------+
| Murray, W    | M    |   27 |
| LaMance, K   | M    |   51 |
| Jones, M     | M    |   29 |
| King, E      | M    |   35 |
| Pitts, D     | M    |   34 |
| Peterson, V  | M    |   30 |
| Cameron, L   | M    |   47 |
| Underwood, K | M    |   60 |
| Derber, B    | M    |   25 |
| Warren, C    | M    |   54 |
+--------------+------+------+
10 rows in set (0.00 sec)

That can also be written a couple of other ways.

mysql> SELECT j.name, j.sex, j.age
    -> FROM junk j, allMale a
    -> WHERE j.name = a.name AND j.sex = a.sex AND j.age = a.age;
+--------------+------+------+
| name         | sex  | age  |
+--------------+------+------+
| Murray, W    | M    |   27 |
| LaMance, K   | M    |   51 |
| Jones, M     | M    |   29 |
| King, E      | M    |   35 |
| Pitts, D     | M    |   34 |
| Peterson, V  | M    |   30 |
| Cameron, L   | M    |   47 |
| Underwood, K | M    |   60 |
| Derber, B    | M    |   25 |
| Warren, C    | M    |   54 |
+--------------+------+------+
10 rows in set (0.00 sec)

Or,

mysql> SELECT DISTINCT j.name, j.sex, j.age
    -> FROM junk j
    -> WHERE EXISTS (SELECT name FROM allMale WHERE j.name = name AND j.sex = sex 
       and j.age = age);
+--------------+------+------+
| name         | sex  | age  |
+--------------+------+------+
| Murray, W    | M    |   27 |
| LaMance, K   | M    |   51 |
| Jones, M     | M    |   29 |
| King, E      | M    |   35 |
| Pitts, D     | M    |   34 |
| Peterson, V  | M    |   30 |
| Cameron, L   | M    |   47 |
| Underwood, K | M    |   60 |
| Derber, B    | M    |   25 |
| Warren, C    | M    |   54 |
+--------------+------+------+
10 rows in set (0.00 sec)

One other note, because SAS, always is just so cute. You can write an INTERSECT with SAS as follows:

proc sql;
SELECT name, age
FROM mysql.admit
WHERE sex = 'M'
INTERSECT ALL
SELECT name, age
FROM mysql.admit;
quit;

Name                 Age
------------------------
Cameron, L            47
Derber, B             25
Jones, M              29
King, E               35
LaMance, K            51
Murray, W             27
Peterson, V           30
Pitts, D              34
Underwood, K          60
Warren, C             54

“We’re developing a new citizenry. One that will be very selective about cereals and automobiles, but won’t be able to think.”
Rod Serling

SAS is particularly vague about what ALL does in the case of INTERSECT but it’s essentially the same as UNION and UNION ALL. In other words, it returns all records that match both tables in the INTERSECT rather than only unique records which is what a simple INTERSECT would do.

SQL Server does not support INTERSECT ALL and MySQL does not support INTERSECT at all.

I hope this is clear to anyone reading this. I always found SET operators really clean. Unfortunately, we’re about to enter the Twilight Zone as SAS has one that is unique to them, but then, that’s “situation normal” for SAS.

Leave a Reply

Your email address will not be published. Required fields are marked *