Substitutes for INTERCEPT in MySQL

We had an interesting assignment in my SQL class this week.

Perform some Web searches using credible trade journals, and other reputable resources to investigate alternatives to using the INTERSECT operator with MySQL and other SQL implementations that do not support it.

Feel free to work out new examples using any of the databases from the course.

  • Post a sample INTERSECT query and an equivalent query using your proposed solution.
  • Explain how your proposed alternative works.
  • Does it produce the exact same results as INTERSECT?
  • If not, how does it differ?

My response:

I looked at several sites for examples of INTERSECT. It can be used in different ways. For me, the most interesting use I found was on the PSOUG site:[1]

“The query below intersects the result set of two SELECT statements to find the common JOB IDs in department ’20’ and ’30’:

SELECT JOB FROM EMPLOYEE
WHERE DEPT = 30
INTERSECT
SELECT JOB FROM EMPLOYEE
WHERE DEPT = 20”

Our textbook uses a similar example, where INTERSECT is used to find employees with a department_id = 90 and a last name that starts with K.

I find these problems too hard to think about without a concrete example, so I went back to the henrybooks database we worked with earlier in the class and made up an example of my own. My example: Find publishers that had published both fiction and horror books (‘fic’ and ‘hor’ types). I then used MySQL select queries just to see what data would fit the criteria:

mysql> select publisher_code
    -> from book
    -> where type ='fic'
    -> order by publisher_code;
+----------------+
| publisher_code |
+----------------+
| HC             |
| LB             |
| LB             |
| LB             |
| PE             |
| PE             |
| PE             |
| PL             |
| PL             |
| PL             |
| SC             |
| VB             |
| VB             |
+----------------+
13 rows in set (0.00 sec)
 
mysql> select publisher_code
    -> from book
    -> where type ='hor';
+----------------+
| publisher_code |
+----------------+
| FA             |
| SC             |
| RH             |
+----------------+
3 rows in set (0.00 sec)

We can see that ‘SC’ is the only publisher_code that publishes both fiction and horror, so now we know what the answer should be!

Using INTERSECT:

select publisher_code from book
where type = ‘fic’
intersect
select publisher_code from book
where type = ‘hor’;

I then tested quite a few alternatives in MySQL before I found one that would produce the correct output, following a model I found on StackOverflow:[2]

mysql> select distinct publisher_code
    -> from book
    -> where publisher_code in (select publisher_code from book where type = 'fic')
    -> and publisher_code in (select publisher_code from book where type = 'hor');
+----------------+
| publisher_code |
+----------------+
| SC             |
+----------------+
1 row in set (0.01 sec)

I’m pleased that the MySQL alternative does seem to produce the exact same results as INTERSECT would. My version uses subqueries to get publisher codes where the type is fiction or horror, which are then limited by the DISTINCT keyword.

I’m sure my alternative has limitations, especially since there are uses of INTERSECT that use two tables, rather than the one table that my example uses. I’m not sure there’s a “correct” answer here, but rather there are approaches that are useful in some cases, but not in others.

[1] http://psoug.org/definition/INTERSECT.htm

[2] https://stackoverflow.com/questions/2300322/intersect-in-mysql#2300339

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s