CODEWITHSUNDEEP
mysqlsql
user2667066
user2667066

Reputation: 2079

SQL multiple betweens joined by or

I have a hierarchy stored as a nested set in a mySQL database, and want to find all the parents of a node, which I can do like this:

SELECT id FROM nested_set WHERE 837461 BETWEEN lft AND rgt;

But I want to do this for a potentially large number of nodes, say 10,000. I can do this by chaining a set of BETWEENs together, e.g.

SELECT id FROM nested_set WHERE 
    44102 BETWEEN lft AND rgt OR
    837461 BETWEEN lft AND rgt OR
    164462 BETWEEN lft AND rgt OR
    566562 BETWEEN lft AND rgt OR
    768916 BETWEEN lft AND rgt OR 
    ...

But this seems very tedious and I guess could exceed the SQL statement size. Is there any more efficient way to do this, rather than creating a query with 10,000 chained OR statements?

Upvotes: 1

Views: 86

Answers (1)

sticky bit
sticky bit

Reputation: 37472

I'm not sure if this qualifies as an improvement or even solution to you, but I'll give it a shot: As I already commented you could use a temporary table storing the nodes.

CREATE TEMPORARY TABLE nodes
                       (node integer,
                        INDEX (node));

INSERT INTO nodes
            (node)
            VALUES (44102),
                   (837461),
                   (164462),
                   (566562),
                   ...;

You could then use it to do your SELECT with an inner join.

SELECT ns.id
       FROM nested_set ns
            INNER JOIN nodes n
                       ON n.node BETWEEN ns.lft
                                         AND ns.rgt);

Note that you may need to use DISTINCT if the nodes aren't unique or more than one given node is in one and the same range.

An alternative uses EXISTS.

SELECT ns.id
       FROM nested_set ns
       WHERE EXISTS (SELECT *
                            FROM nodes n
                            WHERE n.node BETWEEN ns.lft
                                                 AND ns.rgt);

The index on the temporary table may be supporting performance. For the table nested_set I'd try indexes on (lft, rgt, id), (lft, rgt) or at least (lft).

Of course you could also use a derived table of UNION ALLs like

...    
(SELECT 44102 node
 UNION ALL
 SELECT 837461 node
 UNION ALL
 SELECT 164462
 UNION ALL
 SELECT 566562
 ...) nodes
...

to join or select existence from. But then there'd be no supporting index as in the temporary table. With smaller sets however that index may not have so much weight anyways.

Upvotes: 1

Related Questions