Является весь SQL Геопространственной конкретной базой данных реализаций?

Это будет работать для MySql 8.0:

with 
  farmers as (
    SELECT row_number() over ( order by null ) rn, farmer, field from farmers
    WHERE field = 'apple'
  ),    
  sales as (
    SELECT row_number() over ( order by null ) rn, fruit, amount from sales
    WHERE fruit = 'apple'
  )
select s.fruit, f.farmer, s.amount
from sales s left join farmers f
on f.rn = s.rn

См. Демонстрацию
Результаты:

| fruit | farmer | amount |
| ----- | ------ | ------ |
| apple | ben    | 50     |
| apple | joanna | 30     |
| apple | null   | 30     |

Редактировать [ 1110] для MySql 5.5:

select s.fruit, f.farmer, s.amount
from (
  select (@row_number1:=@row_number1 + 1) AS rn, fruit, amount
  from sales, (select @row_number1:=0) t
  where sales.fruit = 'apple'
) s left join (
  select (@row_number2:=@row_number2 + 1) AS rn, farmer
  from farmers, (select @row_number2:=0) t
  where farmers.field = 'apple'
) f
on f.rn = s.rn

См. Демонстрацию