SQL - how to select rows based upon values from a different table?

Question

I've a table called ranges which looks like this-

create table ranges(low bigint, high bigint, id int);
insert into ranges values (10,20,100);
insert into ranges values (21,30,101);

I've another table which looks like this-

create table ip(ip bigint);
insert into ip values (12);

I want a query which will output the id from the ranges table if the ip from ip table is between low and high of ranges table.

For example, for the ip 12, I want the output to be -

12,100

since 12 is between the low 10 and high 20 from the ranges table. What is the most efficient way of doing this? the column ip doesn't exist in the ranges table so I can't do a straightforward join.

ydoow · Accepted Answer · 2016-12-16 00:13:40Z

3

You can join a table using range.

SELECT
    ip.ip, ranges.id
FROM
    ip
JOIN ranges ON ip.ip BETWEEN ranges.low AND ranges.high

answered Dec 16, 2016 at 0:13

ydoow

2,9964 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user375868 Over a year ago

Although it works, on RedShift, it is very slow. I am getting warning- Nested Loop Join in the query plan - review the join predicates to avoid Cartesian products

ydoow Over a year ago

Right, the join like this is not recommended in large table. Alternatively, if there's a regular interval in ranges. You may round up your ip.ip to nearest interval base and do a JOIN.

Collectives™ on Stack Overflow

SQL - how to select rows based upon values from a different table?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related