Почему не работает RIGHT JOIN?

Question

[Губернатор]

Есть таблица задач - issue, при изменении поля в issue, это изменение фиксируется в таблице change_item, где описывается характер изменений, так как в одной задаче за раз можно изменить несколько (группу) полей, групповые изменения будут иметь одну общую группу, фиксируется это в таблице change_group.

Тут код для создания тестовых таблиц

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();

Мне нужно получить информацию о изменении статусов у задач с ID (1,2,3,4,5), и если у задачи статус не менялся, она все равно должна быть в списке.
Проблема в том, что при INNER JOIN при отсутствии сопоставимой записи, выкидывает другую часть, из-за этого у меня в выборку не попадают задачи, у которых статус еще ни разу не менялся.
Я хотел решите проблему с помощью RIGHT JOIN что бы задачи с права были включены, не зависимо есть ли у них изменения статуса или нет Сделав такой запрос:

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;

Я получаю только 1 запись, с ИД 2, у которой менялся статус, я не понимаю почему RIGHT JOIN не заставляет отображать еще 4 записи у которых нет сопоставимых строк в change_item.

Comments

[Melkij]

Внимательно посмотрите на свой where. Что он сделает с появившимися от right join строками с cg.issue_id is null?

Answer 1

[Gip]

конструкция INNER JOIN вам уже ограничила задачи только теми что есть в таблице change_item и RIGHT JOIN вам тут вообще не поможет.

Answer 2

[Rsa97]

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`;