Intersection of SQL statements efficiently

I'm trying to solve a SQL problem where I need to select the data in a certain intersection. This is more or less my query:

SELECT id FROM some_table WHERE
id IN (SELECT id FROM other_table WHERE _id=1 AND some_value >= 1 AND some_value <= 10) AND
id IN (SELECT id FROM another_table WHERE _id=2 AND some_other_value >= -65 AND some_other_value <= -2)

The problem with that query is that it does not return the intersection because the _id field is different both subqueries. So that query will return an empty result always.

If I use OR instead of AND to "intersect" the subqueries, then the intersection is also not returned.

I also do not want to use INTERSECT because that's kind of slow and this is just a very reduced example of a very large query, so speed is really important.

So, my question is, is there any way to do this intersection as fast as possible? Have in mind that even though in the example only appears two subqueries, in my real use case the number of subqueries can be larger.


I'm still unclear on what you're after w/o sample data expected results: but would either of these options work?

inner join ...

SELECT id 
FROM some_table A
INNER JOIN (SELECT id FROM other_table WHERE _id=1 AND some_value >= 1 AND some_value <= 10) B on A.ID = B.ID
INNER JOIN (SELECT id FROM another_table WHERE _id=2 AND some_other_value >= -65 AND some_other_value <= -2) C 
 on C.ID = A.ID

intersect

SELECT id FROM some_table
INTERSECT
SELECT id FROM other_table WHERE _id=1 AND some_value >= 1 AND some_value <= 10
INTERSECT 
SELECT id FROM another_table WHERE _id=2 AND some_other_value >= -65 AND some_other_value <= -2

and then I suppose we could use exists and correlated subquerey instead of IN; which can be faster as it can early escape where an in does not.