Как вывести элемент, если нет связи many to many?

Question

[Davidaa_WoW]

Есть значит следующая БД со связью many to many:

create table products (product_id integer NOT NULL, title varchar(100), PRIMARY KEY (product_id));
create table categories (category_id integer NOT NULL, title varchar(100), PRIMARY KEY (category_id));
create table products_category 
(
	product_id integer NOT NULL,
	category_id integer NOT NULL,
  	PRIMARY KEY (product_id, category_id),
  	FOREIGN KEY (product_id) REFERENCES products(product_id),
  	FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

insert into categories (category_id, title) values (1, 'category1');
insert into categories (category_id, title) values (2, 'category2');
insert into categories (category_id, title) values (3, 'category3');
insert into categories (category_id, title) values (4, 'category4');

insert into products (product_id, title) values (1, 'product1');
insert into products (product_id, title) values (2, 'product2');
insert into products (product_id, title) values (3, 'product3');
insert into products (product_id, title) values (4, 'product4');

insert into products_category (product_id, category_id) values (1, 1);
insert into products_category (product_id, category_id) values (1, 2);
insert into products_category (product_id, category_id) values (1, 3);
insert into products_category (product_id, category_id) values (2, 3);
insert into products_category (product_id, category_id) values (2, 4);
insert into products_category (product_id, category_id) values (3, 1);

Составляю запрос:

select products.title, categories.title from products
inner join products_category on products_category.product_id = products.product_id
inner join categories on products_category.category_id = categories.category_id;

Вроде бы всё работает, однако не выводится product4, у которого нет ни одной категории, так вот, мне нужно, чтобы и он тоже выводился

Comments

[Akina]

Я лично в упор не вижу ТРЕБУЕМОГО РЕЗУЛЬТАТА для выложенных исходных данных.

не выводится product4, у которого нет ни одной категории, так вот, мне нужно, чтобы и он тоже выводился

Добавь ещё в исходные данные категорию, к которой не относится ни один продукт (category5). И вот только после этого - показывай требуемый результат.

[Slava Rozhnev]

Akina, автору плюс за опубликованный SQL

[Akina]

Slava Rozhnev, с этим - согласен. Достаточно редко встречается хорошо оформленный вопрос.

Answer 1

[Rsa97]

LEFT JOIN

Answer 2

[Akina]

Шаблон при решении такого рода задач следующий:

SELECT *
FROM products 
CROSS JOIN categories
LEFT JOIN products_category USING (product_id, category_id)

CROSS JOIN генерирует все возможные комбинации, а потом LEFT JOIN либо присоединяет к комбинации реально существующие данные, либо нет - тогда в соотв. полях одни NULL. Ну а что именно выводить - это уже решается по конкретной задаче.

Comments

[Rsa97]

То есть вы предлагаете сначала получить полное декартово произведение продуктов и категорий, потом ещё навернуть сверху таблицу связи и фильтровать это всё в приложении? Вообще-то это делается наоборот

SELECT *
  FROM products
  LEFT JOIN products_category USING (product_id)
  LEFT JOIN categories USING (category_id)

[Akina]

Rsa97,

Вообще-то это делается наоборот

Ну и фигня получилась. Второй JOIN превратил LEFT в INNER. Как итог - ни продуктов без категорий, ни категорий без продуктов в выходном наборе не будет.

Сравни: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f672c7...

[Rsa97]

Akina, Дв, второй тоже должен быть LEFT, тут я тупанул.

[Akina]

Rsa97, да пжалста: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=444ffb...
Второй сделан тоже LEFT, 'product4' появился, а 'category5' в результате - отсутствует.

[Rsa97]

Akina, Так у ТС задача вывести все продукты, а не все категории.

[Akina]

Rsa97, возможно. А возможно, что он жалуется только на вывод не всех продуктов исключительно потому, что ещё не обнаружил, что и категории могут выводиться не все... в любом случае я специально указал, что это общий шаблон, а не готовое решение.