How to print row(s) if they meet a certain range

You may use this awk:

awk 'BEGIN{FS=OFS=","} FNR==NR {if (FNR>1) {chr[++n] = $1; id[n]=$2; r1[n]=$3; r2[n]=$4}; next} {for (i=1; i<=n; ++i) if ($1 == chr[i] && r1[i] > $2 && r2[i] < $3) {$0 = $0 OFS id[i] OFS r1[i] OFS r2[i]; break}} 1' file2 file1

chr1,3073253,3074322,gene_id,"ENSMUSG00000102693.1",gene_type,"TEC",linc1320,3073300,3074300
chr1,3074253,3075322,gene_id,"ENSMUSG00000102693.1",transcript_id,"ENSMUST00000193812.1"
chr1,3077253,3078322,gene_id,"ENSMUSG00000102693.1",transcript_id,"ENSMUST00000193812.1"
chr1,3102916,3103025,gene_id,"ENSMUSG00000064842.1",gene_type,"snRNA"
chr1,3105016,3106025,gene_id,"ENSMUSG00000064842.1",transcript_id,"ENSMUST00000082908.1"

A more readable form:

awk '
BEGIN { FS = OFS = "," }
FNR == NR {
   if (FNR > 1) {
      chr[++n] = $1
      id[n] = $2
      r1[n] = $3
      r2[n] = $4
   }
   next
}
{
   for (i=1; i<=n; ++i)
      if ($1 == chr[i] && r1[i] > $2 && r2[i] < $3) {
         $0 = $0 OFS id[i] OFS r1[i] OFS r2[i]
         break
      }
} 1' file2 file1

Let us try approaching the problem pandas way, first read the csv files into a pandas dataframe

f1 = pd.read_csv('file1.csv', header=None)
f2 = pd.read_csv('file2.csv')

>>> f1

      0        1        2        3                     4              5                     6
0  chr1  3073253  3074322  gene_id  ENSMUSG00000102693.1      gene_type                   TEC
1  chr1  3074253  3075322  gene_id  ENSMUSG00000102693.1  transcript_id  ENSMUST00000193812.1
2  chr1  3077253  3078322  gene_id  ENSMUSG00000102693.1  transcript_id  ENSMUST00000193812.1
3  chr1  3102916  3103025  gene_id  ENSMUSG00000064842.1      gene_type                 snRNA
4  chr1  3105016  3106025  gene_id  ENSMUSG00000064842.1  transcript_id  ENSMUST00000082908.1


>>> f2

    chr      name    start      end
0  chr1  linc1320  3073300  3074300
1  chr3  linc2245  3077270  3078250
2  chr1  linc8956  4410501  4406025

Now we can merge and filter the rows which satisfy the given condition of interval inclusion then we can join the filtered rows with the file f1

m = f1.reset_index()\
      .merge(f2, left_on=0, right_on='chr')\
      .where(lambda x: x[1].le(x['start']) & x[2].ge(x['end']))\
      .set_index('index')[['name', 'start', 'end']]

f3 = f1.join(m)

>>> f3

      0        1        2        3                     4              5                     6      name      start        end
0  chr1  3073253  3074322  gene_id  ENSMUSG00000102693.1      gene_type                   TEC  linc1320  3073300.0  3074300.0
1  chr1  3074253  3075322  gene_id  ENSMUSG00000102693.1  transcript_id  ENSMUST00000193812.1       NaN        NaN        NaN
2  chr1  3077253  3078322  gene_id  ENSMUSG00000102693.1  transcript_id  ENSMUST00000193812.1       NaN        NaN        NaN
3  chr1  3102916  3103025  gene_id  ENSMUSG00000064842.1      gene_type                 snRNA       NaN        NaN        NaN
4  chr1  3105016  3106025  gene_id  ENSMUSG00000064842.1  transcript_id  ENSMUST00000082908.1       NaN        NaN        NaN

PS: You can also save the resulting dataframe f3 to a csv file using f3.to_csv('file3.csv')

EDIT: With OP's edited Input, one could try following. This will work even fields are more than 4 in file2.

awk '
BEGIN{
  FS=OFS=","
}
FNR==NR{
  start[++count]=$2
  end[count]=$3
  match($0,/,.*/)
  val[count]=substr($0,RSTART-1,RLENGTH-1)
  next
}
{
  for(i=1;i<=count;i++){
    if(start[i]>$2 && end[i]<$3){
      print $0 OFS val[i]
      next
    }
  }
}
1' file2 file1

With your shown samples, could you please try following. Written and tested in GNU awk, should work in any awk. Taken reference from anubhava's answer.

awk '
BEGIN{
  FS=OFS=","
}
FNR==NR{
  start[++count]=$2
  end[count]=$3
  val[count]=$0
  next
}
{
  for(i=1;i<=count;i++){
    if(start[i]>$2 && end[i]<$3){
      print $0 OFS val[i]
      next
    }
  }
}
1' file2 file1

Explanation: Adding detailed explanation for above.

awk '                                  ##Starting awk program from here.
BEGIN{                                 ##Starting BEGIN section of this program from here.
  FS=OFS=","                           ##Setting FS and OFS as comma here. 
}
FNR==NR{                               ##Checking condition which will be true when file2 is being read.
  start[++count]=$2                    ##Creating start array with count variable as as index and has $2 value in it.
  end[count]=$3                        ##Creating end array with count as index and value is $3.
  val[count]=$0                        ##Creating val array with index of count and value as $0.
  next                                 ##next will skip all further statements from here.
}
{
  for(i=1;i<=count;i++){               ##Running for loop till value of count here.
    if(start[i]>$2 && end[i]<$3){      ##Checking condition if start[i]>$2 AND end[i]<$3.
      print $0 OFS val[i]              ##Then printing current line with OFS, val here.
      next                             ##next will skip all further statements from here.
    }
  }
}
1                                      ##1 will print current line here.
' file2 file1                          ##Mentioning Input_file names here.