Multiple-item IN operator AKA set intersection in MySQL

Question

mysql> SELECT * FROM nodes
    -> WHERE hostName IN ('localhost', 'm1iafw') OR
    ->        address IN ('localhost', 'm1iafw');
+----------------------+-----------+-----------+
| id                   | hostName  | address   |
+----------------------+-----------+-----------+
|  9131891219333790492 | NULL      | m1iafw    |
| 15289714606300179720 | localhost | NULL      |
| 15886756565768587967 | m1iafw    | 10.7.7.12 |
| 18400354826544934228 | m1iafw    | NULL      |
+----------------------+-----------+-----------+

Above is a simplified version of a query I'm working on. I would like to write this with one IN operator so I don't have to repeat list of host names multiple times. There could be hundreds of host names in the list, potentially.

What I want is something like:

SELECT * FROM nodes
 WHERE (hostName, address) INTERSECTS ('localhost', 'm1iafw');

I don't need the list of matching values like INTERSECT would provide. I just need a boolean result like with IN. Do the sets intersect or not.

Also, MySQL doesn't support the INTERSECT operator. Nor WITH. The usual advice is to rewrite the INTERSECT in a different form using joins, but I don't know how to do that since I'm not intersecting two tables.

Furthermore, what I really want to do is a bit more complex, even. Given the ID numbers of the nodes from the query up top, I want to search another table for matches, where any one of three columns might contain one of the matching IDs. With my hypothetical INTERSECTS operator I could write the query as:

SELECT * FROM alerts
 WHERE (analyzerNodeId, sourceNodeId, targetNodeId) INTERSECTS
           (SELECT id FROM nodes
             WHERE (hostName, address) INTERSECTS ('localhost', 'm1iafw'));

Any suggestions on how I can do this? My SQL-fu is not so strong.

I have to ask why the list is a hard-coded list of strings to begin with. Could the list you are using in the IN clause be pulled from some other db table? If so, then you just replace your string list with a proper sub-query to pull the list you are wanting to limit to. Then it doesn't matter that you have two IN clauses, and in fact this should be a relatively good performing query versus the INTERSECTS approach. — David Fleeman, Nov 08 '13 at 17:22
There's a tree containing a couple hundred checkboxes in my GUI. The user can choose which hosts to filter on. I'm constructing the SQL query based on which checkboxes they've selected. — John Kugelman, Nov 08 '13 at 17:25
I highly recommend reading the answers to this question: http://stackoverflow.com/questions/1532366/mysql-number-of-items-within-in-clause. Exploring mysql temporary tables may make this work a lot better for you if the user does in fact check 200 items. Comparing against that many string literals is expensive operation. — David Fleeman, Nov 08 '13 at 17:27

score 2 · Accepted Answer · answered Nov 08 '13 at 17:24

For such task you need to have "temporary" table, for mysql you can create it like this:

select 'localhost' as hostname
union all
select 'm1iafw'
union all
select ....

so, to fetch all distinct ID where either hostname or address is in this table you can use join syntax:

select id
from nodes as n
inner join (
    select 'localhost' as hostname
    union all
    select 'm1iafw'
) as q
on (n.hostName = q.hostname OR n.address = q.hostname)

then you can use this query as subquery for next operation:

SELECT *
FROM alerts as a
inner join
(
    select id
    from nodes as n
    inner join (
        select 'localhost' as hostname
        union all
        select 'm1iafw'
    ) as q
    ON (n.hostName = q.hostname OR n.address = q.hostname)
) as q2
ON (a.analyzerNodeId=q2.id OR a.sourceNodeId=q2.id OR a.targetNodeId=q2.id)

This is exactly the approach I have used, as well; although calling it a "temporary table" is a bit confusing, as MySQL actually supports something called a temporary table, which is quite separate from this concept. — Jonathan Hall, Nov 08 '13 at 17:34
@Flimzy right, this is why I put temporary in quotes, as it is really in-memory record set — Iłya Bursov, Nov 08 '13 at 17:35

score 0 · Answer 2 · answered Nov 08 '13 at 17:38

0

You could perhaps implement using a FULL-TEXT index...

SELECT * FROM nodes
 WHERE MATCH (hostName,address ) AGAINST ('localhost m1iafw' IN BOOLEAN MODE);

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

answered Nov 08 '13 at 17:38

barryhunter

20,886
3
30
43

Multiple-item IN operator AKA set intersection in MySQL

2 Answers2