user2543622 user2543622 - 1 year ago 59
SQL Question

sql - performing join when matching value is in between two column values

I have a table1 like below, table1 is very huge

Col1 Col2
A 1
B 5
A 7

My table2 is as below. My real table2 is very big and I cannot explode it due to size restrictions.

Col3 Col4 Col5
0 2 x
4 5.5 y
6 7.5 z

In such case is there a way to join table1 and table2 such that
where table1.col2 is between table2.col3 and table2.col4

The output that I want is below

Col1 Col2 Col5
A 1 x
B 5 y
A 7 z

Answer Source

You need INNER JOIN with BETWEEN as join condition

SELECT t1.col1, 
FROM   table1 t1 
       JOIN table2 t2 
         ON t1.col2 BETWEEN t2.col3 AND t2.col4 

Note : you have mentioned where table1>>col2 is between table2>>col4 and table2>>col5 but it should be where table1>>col2 is between table2>>col3 and table2>>col4