20 May

SET Operators: EXCEPT

I was thinking this would be hard going into it, but, it’s actually really easy, except, that EXCEPT doesn’t exist in SQL Server.

EXCEPT is very simple.  What it does is return every unique row in the first result set, that is not in the second result set.  Here are two very simple examples:

SELECT 1 AS r1,2 AS r2,3 AS r3
EXCEPT
SELECT 3,4,5;

r1	r2	r3
1	2	3

SELECT 1 AS r1,2 AS r2,3 AS r3
EXCEPT
SELECT 1,2,3;

r1	r2	r3

Those are simple queries but they show how the EXCEPT operator works. Now, what happens if we have records that are duplicates in the first table? Well, this happens:

SELECT 1 AS r1,2 AS r2,3 AS r3
UNION ALL
SELECT 1,2,3
r1	r2	r3
1	2	3
1	2	3

(
SELECT 1 AS r1,2 AS r2,3 AS r3
UNION ALL
SELECT 1,2,3
)
EXCEPT
SELECT 3,4,5

r1	r2	r3
1	2	3

That’s really clean compared to most of my posts, and, it’s going to stay that way. The reason? I’m not going to mess with SAS and I’m going to let MySQL go right ahead not doing it. If you decide to do it in MySQL, and one of these days, I’ll do it, you need to get a bit creative with a JOIN or a couple of other options such as a correlated subquery and NOT EXISTS.

OK, I talked myself into it, but only because I literally just did this the other day.

First, I’ll create a new table that has just the records where sex = ‘M’

mysql> CREATE TABLE allMale AS SELECT * FROM junk WHERE sex = 'M';
Query OK, 10 rows affected (0.11 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT name, sex, age FROM allMale;
+--------------+------+------+
| 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)

Maybe I shouldn’t have called these tables junk?

Anyways, here is a query in SQL Server using the EXCEPT operator.

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

name	        sex	age
Almers, C	F	34
Bonaventure, T	F	31
Eberhardt, S	F	49
Ivan, H	        F	22
Johnson, R	F	43
Nunnelly, A	F	44
Oberon, M	F	28
Quigley, M	F	40
Reberson, P	F	32
Takahashi, Y	F	43
Wilcox, E	F	41

Now, for the MySQL version.

mysql> SELECT DISTINCT name, sex, age
    -> FROM JUNK j
    -> WHERE NOT EXISTS (SELECT name FROM allMale WHERE j.name = name AND j.
age = age AND j.weight = weight);
+----------------+------+------+
| name           | sex  | age  |
+----------------+------+------+
| Almers, C      | F    |   34 |
| Bonaventure, T | F    |   31 |
| Johnson, R     | F    |   43 |
| Reberson, P    | F    |   32 |
| Eberhardt, S   | F    |   49 |
| Nunnelly, A    | F    |   44 |
| Oberon, M      | F    |   28 |
| Quigley, M     | F    |   40 |
| Takahashi, Y   | F    |   43 |
| Ivan, H        | F    |   22 |
| Wilcox, E      | F    |   41 |
+----------------+------+------+
11 rows in set (0.00 sec)

I had to write that about 10 times because I kept forgetting to change the SELECT clause. Yeah, for me, SELECT = ‘HARD’, EXISTS = ‘Easy’.

The only part of this query that really matters is the WHERE clause. Essentially it uses a correlated subquery to compare the two data sets and where the record NOT EXISTS in allMale, it is returned to the result set. It’s not very clean, and all things being equal, I’d rather use EXCEPT.

OK, the SELECT matters too.  I have to use the DISTINCT there so the query won’t return duplicate values, or, as the documentation I’ve read for EXCEPT says “unique values only”.

And just for kicks, Oracle doesn’t have an EXCEPT operator either. Over on that part of the ranch, they roll with MINUS.

Fortunately, I’m done with EXCEPT, except, until I do the “SAS is special” operator article.

26 Jan

Correlated Subquery For Top N

In my last post I used a CTE on some terrible data (I know that, and, I know this example is even worse) to produce a TOP (2) Sales By Region. I’m going to do the same thing on the same data.

Note: This works because the names are unique. If they weren’t I’d have to have a unique identifier for the rep names. But this isn’t about normalized or tight data, it’s about a correlated subquery. So, here’s the data,

SELECT * FROM mysales

Region	Rep	Sales
reg1	Bob	1000
reg1	Bill	2000
reg1	Joe	3000
reg1	Frank	4000
reg2	Sam	5000
reg2	Jill	6000
reg2	Greg	7000
reg2	Harry	8000

and, again, we want the TOP 2 reps by region. So, here’s how the same query would look using a correlated subquery,

select *
FROM mysales o1
WHERE o1.rep IN (SELECT TOP (2) rep as big
FROM mysales o2
WHERE o1.region = o2.region
ORDER BY o2.sales DESC)
ORDER BY region, sales DESC

Like a CTE this query essentially runs two select statements: the main query and the correlated subquery.  The first item of note is the IN operator.  The IN operator says to match any item in the result set that the subquery returns.  The second thing to note is the WHERE clause in the subquery, it’s how we link the two queries together.  In this case it’s saying to run the subquery against the region in the outer query.  This is how the query knows what region to search against.

To think of it another way, the main query is saying: give us the top 2 reps in each region and it’s doing it with the IN operator and the WHERE clause.

All things considered a CTE is probably easier to write and cleaner to read but not all databases have that functionality available.  It’s very probable that some day you’ll need a correlated subquery for a problem like this.