v2571
Device: MS S2005 sp1 

create table R1
 {
   A:Integer,
   B:Integer,
   C:String,
   key {A}
 };
 
 insert table
        {
          row{1 A,13 B,'A' C},
          row{2,28,'B'},
          row{3,42,'C'}
        }
          into R1;
          
create table R2
 {
   D:Integer,
   E:Integer,
   F:Integer,
   G:String,
   key{D}
 };  
   
insert table
        {
          row{1 D,1 E,8 F,'D' G},
          row{2,1,10,'E'},
          row{3,1,15,'F'},
          row{4,1,21,'G'},
          row{5,1,25,'H'},
          row{6,2,20,'I'},
          row{7,2,27,'J'},
	  row{8,2,31,'K'},
	  row{9,2,38,'L'}
       }
          into R2;        
          
//Obtain the top 2 F for each A with a constraint using APPLY.
select SQLQuery
('
 select A,B,C,D,E,F,G
  from R1
     cross apply
         (select top 2 D,E,F,G
            from R2
              where A=E and B>F
                order by F desc) as Y
 order by A,F
 ');
/*
A B  C D E F  G 
- -- - - - -- - 
1 13 A 1 1 8  D 
1 13 A 2 1 10 E 
2 28 B 6 2 20 I 
2 28 B 7 2 27 J 
*/

//Without APPLY in D4.        
select R1 join R2   
            by A=E  
//Redundant with APPLY.
//The idea being that some form of constraint
//must be used to limit the rows from the join.
  where
    table {row{A X,D Y}}
         <=
    (  
      R2 where A=E and B>F    
        return 2 by {F desc}  
         with {IgnoreUnsupported = 'true'}
          {E X,D Y}
     );
/*     
A B  C D E F  G 
- -- - - - -- - 
1 13 A 1 1 8  D 
1 13 A 2 1 10 E 
2 28 B 6 2 20 I 
2 28 B 7 2 27 J 
*/

APPLY seems to always underlie a join and a constraint. It shows 
the constraint to be redundant. The where in the constraint is 
redundant with the join condition let alone the unnecessary 
repetition of the table.

Perhaps something like this in D4:

select 
    R1
      join
          (
            R2
              Entry by A=E 
                 where B>F 
                    return 2 by {F desc}  
	  );
          
The 'Entry' keyword would indicate that the attribute 'SupportsNestedCorrelation'          
is true. The right table would be entered by the join condition
A=E  which is both a join condition and a constraint. The Entry keyword
would allow the where to include column(s) from the left table.
Instead of in-line, an operator could return the table. Entry would
simply indicate that column(s) from the left table could be passed to
the operator. 
It would appear that the semantics of join could incorporate the idea
of apply (cross apply->join, outer apply->outer join, in-line or operator.