-- Создание таблиц

CREATE TABLE JOIN_TBL (
    ID    INTEGER NOT NULL,
    TEXT  VARCHAR(4096)
);

ALTER TABLE JOIN_TBL ADD CONSTRAINT PK_TBL PRIMARY KEY (ID);

CREATE TABLE TBL (
    ID    INTEGER NOT NULL,
    TEXT  VARCHAR(4096)
);

ALTER TABLE TBL ADD CONSTRAINT FK_TBL FOREIGN KEY (ID) REFERENCES JOIN_TBL (ID);

create generator GEN_JOIN_TBL_ID;

-- Заполнение тестовыми данными

set term ^;

execute block as
declare variable i integer;
begin
  i = 0;
  while (i < 1000000) do
  begin
    insert into join_tbl(id,text) values (gen_id(GEN_JOIN_TBL_ID, 1), cast(rand() as varchar(30)));
    i = i + 1;
  end
end^

insert into tbl select * from join_tbl order by cast(text as double precision)^

-- Тестовые процедуры, иллюстрирующие работу JOIN

create procedure TEST_INDEX
returns (text varchar(4096), join_text varchar(4096))
as
  DECLARE tbl CURSOR FOR (select id, text from tbl order by id);
begin
  open tbl;
  while (true) do
  begin
    fetch tbl;
    if (row_count = 0) then break;
    select text from join_tbl where id = tbl.id into :join_text;
    text = tbl.text;
    suspend;
  end
end^

create procedure TEST_MERGE
returns (text varchar(4096), join_text varchar(4096))
as
  DECLARE tbl CURSOR FOR (select id, text from tbl order by id);
  DECLARE join_tbl CURSOR FOR (select id, text from join_tbl order by id);
begin
  open tbl;
  open join_tbl;
  fetch tbl;
  fetch join_tbl;
  while (true) do
  begin
    if (tbl.id = join_tbl.id) then
    begin
      text = tbl.text;
      join_text = join_tbl.text;
      suspend;
      fetch tbl;
      if (row_count = 0) then break;
    end else
    begin
      if (tbl.id < join_tbl.id) then
        fetch tbl;
      else
        fetch join_tbl;
      if (row_count = 0) then break;
    end
  end
end^