Why doesn't RIGHT JOIN work?

Question

PostgreSQL

There is a task table - issue, when a field is changed in an issue, this change is recorded in the change_item table, which describes the nature of the changes, since several (group) fields can be changed at a time in one task, group changes will have one common group, this is fixed in change_group table.

Here is the code for creating test tables

create sequence change_group_seq;
create sequence change_item_seq;


create table issue
(
  id			integer not null,
  name		varchar(255),
  "desc"		varchar(255),
  status		varchar(255),
  author		varchar(255),
  created_at	timestamp default now()
);

alter table issue
  owner to postgres;

INSERT INTO public.issue (id, name, "desc", status, author, created_at)
VALUES (11, 'Сдать кровь', null, 'Готово', 'admin', '2020-10-28 16:56:23.640164'),
(13, 'Выкинуть строй мусор', null, 'Частично', 'admin', '2020-10-28 16:56:23.640164'),
(10, 'Купить тапки в офис', null, 'Готово', 'admin', '2020-10-28 16:56:23.640164'),
(14, 'Убраться в комнате', null, 'Частично', 'admin', '2020-10-28 16:56:23.640164'),
(2, 'Починить машину', null, 'В процессе', 'admin', '2020-10-28 16:56:23.640164'),
(12, 'Накачать трицепс', null, 'Готово', 'admin', '2020-10-28 16:56:23.640164'),
(8, 'Оплатить учебу', '+2000 давал взятку', 'Готово', 'admin', '2020-10-28 16:56:23.640164'),
(1, 'Купить телефон', 'Только не нокию', null, 'admin', '2020-10-28 16:56:23.640164'),
(3, 'Вернуть деньги Саше', '200 руб', null, 'admin', '2020-10-28 16:56:23.640164'),
(4, 'Скачать фотошоп', null, null, 'admin', '2020-10-28 16:56:23.640164'),
(5, 'Переплыть байкал', null, null, 'admin', '2020-10-28 16:56:23.640164'),
(6, 'Разобрать кровать', null, null, 'admin', '2020-10-28 16:56:23.640164'),
(7, 'Купить пылесос', null, null, 'admin', '2020-10-28 16:56:23.640164'),
(9, 'Сходить к тёте Маше', null, null, 'admin', '2020-10-28 16:56:23.640164'),
(15, 'Съездить в военкомат', 'Отметится', null, 'admin', '2020-10-28 16:56:23.640164');



create table change_group
(
  id         integer,
  issue_id   integer,
  author     varchar(255),
  created_at timestamp default now()
);

alter table change_group
  owner to postgres;

INSERT INTO public.change_group (id, issue_id, author, created_at) VALUES (1, 1, ' admin', '2020-10-28 18:31:09.660089'),
(2, 2, ' admin', '2020-10-28 18:31:09.728848'),
(3, 3, ' admin', '2020-10-28 18:31:09.808709'),
(4, 4, ' admin', '2020-10-28 18:31:09.880535'),
(5, 5, ' admin', '2020-10-28 18:31:09.945120'),
(6, 6, ' admin', '2020-10-28 18:31:10.044606'),
(7, 7, ' admin', '2020-10-28 18:31:10.082836'),
(8, 8, ' admin', '2020-10-28 18:31:10.145965'),
(9, 9, ' admin', '2020-10-28 18:31:10.191415'),
(10, 10, ' admin', '2020-10-28 18:31:10.252789'),
(11, 11, ' admin', '2020-10-28 18:31:10.325322'),
(12, 12, ' admin', '2020-10-28 18:31:10.362965'),
(13, 13, ' admin', '2020-10-28 18:31:10.426822'),
(14, 14, ' admin', '2020-10-28 18:31:10.490756'),
(15, 15, ' admin', '2020-10-28 18:31:10.571724'),
(16, 12, ' admin', '2020-10-28 18:36:42.126994'),
(17, 11, ' admin', '2020-10-28 18:36:59.136164'),
(18, 13, ' admin', '2020-10-28 18:37:15.739650'),
(19, 8, ' admin', '2020-10-28 18:37:28.181517'),
(20, 10, ' admin', '2020-10-28 18:37:41.888977'),
(21, 14, ' admin', '2020-10-28 18:38:08.628611'),
(22, 2, ' admin', '2020-10-28 18:38:34.662460'),
(23, 12, ' admin', '2020-10-28 18:38:40.634004'),
(24, 8, ' admin', '2020-10-28 18:39:29.791065');



create table change_item
(
  id        integer,
  group_id  integer,
  field     varchar(255),
  old_value varchar(255),
  new_value varchar(255)
);

alter table change_item
  owner to postgres;

INSERT INTO public.change_item (id, group_id, field, old_value, new_value)
VALUES (21, 18, 'status', null, 'Частично'),
(12, 10, 'name', null, 'Купить тапки в офис'),
(14, 12, 'name', null, 'Накачать трицепс'),
(27, 24, 'desc', null, '+2000 давал взятку'),
(20, 17, 'status', null, 'Готово'),
(18, 15, 'desc', null, 'Отметится'),
(2, 1, 'desc', null, 'Только не нокию'),
(28, 24, 'status', 'Частично', 'Готово'),
(23, 20, 'status', null, 'Готово'),
(17, 15, 'name', null, 'Съездить в военкомат'),
(8, 6, 'name', null, 'Разобрать кровать'),
(13, 11, 'name', null, 'Сдать кровь'),
(7, 5, 'name', null, 'Переплыть байкал'),
(26, 23, 'status', 'В процессе', 'Готово'),
(10, 8, 'name', null, 'Оплатить учебу'),
(6, 4, 'name', null, 'Скачать фотошоп'),
(4, 3, 'name', null, 'Вернуть деньги Саше'),
(15, 13, 'name', null, 'Выкинуть строй мусор'),
(25, 22, 'status', null, 'В процессе'),
(3, 2, 'name', null, 'Починить машину'),
(24, 21, 'status', null, 'Частично'),
(22, 19, 'status', null, 'Частично'),
(11, 9, 'name', null, 'Сходить к тёте Маше'),
(16, 14, 'name', null, 'Убраться в комнате'),
(1, 1, 'name', null, 'Купить телефон'),
(19, 16, 'status', null, 'В процессе'),
(9, 7, 'name', null, 'Купить пылесос'),
(5, 3, 'desc', null, '200 руб');



CREATE OR REPLACE FUNCTION changedIssueField()
  RETURNS TRIGGER
AS
$$
DECLARE
  group_id_seq integer = nextval(change_group_seq);
BEGIN
  INSERT INTO change_group (id, issue_id, author) VALUES (group_id_seq, new.id, 'admin');

  IF new.name != old.name THEN
    INSERT INTO change_item (id, group_id, field, old_value, new_value)
    VALUES ( nextval(change_item_seq), group_id_seq, 'name', old.name, new.name );
  END IF;

  IF new."desc" != old."desc" THEN
    INSERT INTO change_item (id, group_id, field, old_value, new_value)
    VALUES ( nextval(change_item_seq), group_id_seq, 'desc', old."desc", new."desc" );
  END IF;

  IF new.status != old.status THEN
    INSERT INTO change_item (id, group_id, field, old_value, new_value)
    VALUES ( nextval(change_item_seq), group_id_seq, 'status', old.status, new.status );
  END IF;

  IF new.author != old.author THEN
    INSERT INTO change_item (id, group_id, field, old_value, new_value)
    VALUES ( nextval(change_item_seq), group_id_seq, 'author', old.author, new.author );
  END IF;

  RETURN new;
END;
$$;
  LANGUAGE plpgsql;
CREATE TRIGGER update_issue
  BEFORE UPDATE
  ON issue
  FOR EACH ROW
  EXECUTE PROCEDURE changedIssueField();

I need to get information about status changes for tasks with ID (1,2,3,4,5), and if the status of the task has not changed, it should still be in the list.
The problem is that with INNER JOIN, in the absence of a comparable record, it throws out the other part, because of this, my selection does not include tasks whose status has never changed.
I wanted to solve the problem using RIGHT JOIN so that the tasks on the right are included, regardless of whether they have status changes or not By making a query like this:

SELECT *
FROM change_group cg
INNER JOIN change_item ci ON cg.id = ci.group_id AND ci.field = 'status'
RIGHT JOIN issue i ON cg.issue_id = i.id --Не работает, если его закоментировать ничего не изменится.
WHERE cg.issue_id IN (1,2,3,4,5)
ORDER BY cg.issue_id, cg.created_at;

I only get 1 entry, with ID 2, which had a status change, I don't understand why RIGHT JOIN doesn't make it show 4 more entries that don't have comparable rows in change_item.

Answer 1

the INNER JOIN construction has already limited your tasks to only those that are in the change_item table and RIGHT JOIN will not help you here at all.

Answer 2

SELECT *
  FROM `change_group` AS `cg`
  JOIN `change_item` AS `ci`
    ON `ci`.`group_id` = `cg`.`id` AND `ci`.`field` = 'status'
  RIGHT JOIN `issue` AS `i`
    ON `i`.`id` = `cg`.`issue_id`
  WHERE `i`.`id` IN (1,2,3,4,5)
  ORDER BY `i`.`id`, `cg`.`created_at`;