Oracle BETWEEN from subquery-CodePudding

Is something like this possible?

SELECT *
FROM [TABLE1]
WHERE [FIELDA] BETWEEN
    (SELECT [FIELDB], [FIELDC] 
     FROM [TABLE2]
     WHERE [set of conditions to ensure single record]);

Instead of doing:

SELECT *
FROM [TABLE1]
WHERE [FIELDA]
  BETWEEN (SELECT [FIELDB] FROM [TABLE2] WHERE [set of conditions to ensure single record])
  AND (SELECT [FIELDB] FROM [TABLE2] WHERE [set of conditions to ensure single record]);

Thanks,

CodePudding user response：

Another option would be to move the BETWEEN condition into a correlated subquery:

SELECT *
FROM [TABLE1]
WHERE EXISTS
    (SELECT NULL  
     FROM [TABLE2]
     WHERE [set of conditions to ensure single record]
       AND [TABLE1].[FIELDA] BETWEEN [TABLE2].[FIELDB] AND [TABLE2].[FIELDC]);

In this case it's not strictly necessary to limit the subquery to a single record. It could produce any number of records and the condition would be true as long as that number is more than 0.

CodePudding user response：

Something like this might do:

with temp as 
  (select fieldb, fieldc
   from table2
   where set_of_conditions
  )
select *
from table1 a join temp b on a.fielda between b.fieldb and b.fieldc;