MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 categories (category_id, title) values (5, 'category5');

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.*,
       categories.*,
       products_category.*
  FROM products
  LEFT JOIN products_category USING (product_id)
  JOIN categories USING (category_id)


product_id
title
category_id
title
product_id
category_id


1
product1
1
category1
1
1

1
product1
2
category2
1
2

1
product1
3
category3
1
3

2
product2
3
category3
2
3

2
product2
4
category4
2
4

3
product3
1
category1
3
1


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


product_id
title
category_id
title
product_id
category_id


4
product4
1
category1
null
null

3
product3
1
category1
3
1

2
product2
1
category1
null
null

1
product1
1
category1
1
1

4
product4
2
category2
null
null

3
product3
2
category2
null
null

2
product2
2
category2
null
null

1
product1
2
category2
1
2

4
product4
3
category3
null
null

3
product3
3
category3
null
null

2
product2
3
category3
2
3

1
product1
3
category3
1
3

4
product4
4
category4
null
null

3
product3
4
category4
null
null

2
product2
4
category4
2
4

1
product1
4
category4
null
null

4
product4
5
category5
null
null

3
product3
5
category5
null
null

2
product2
5
category5
null
null

1
product1
5
category5
null
null


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


product_id
title
category_id
title
product_id
category_id


1
product1
1
category1
1
1

1
product1
2
category2
1
2

1
product1
3
category3
1
3

2
product2
3
category3
2
3

2
product2
4
category4
2
4

3
product3
1
category1
3
1

4
product4
null
null
null
null

product_id	title	category_id	title	product_id	category_id
1	product1	1	category1	1	1
1	product1	2	category2	1	2
1	product1	3	category3	1	3
2	product2	3	category3	2	3
2	product2	4	category4	2	4
3	product3	1	category1	3	1

product_id	title	category_id	title	product_id	category_id
4	product4	1	category1	null	null
3	product3	1	category1	3	1
2	product2	1	category1	null	null
1	product1	1	category1	1	1
4	product4	2	category2	null	null
3	product3	2	category2	null	null
2	product2	2	category2	null	null
1	product1	2	category2	1	2
4	product4	3	category3	null	null
3	product3	3	category3	null	null
2	product2	3	category3	2	3
1	product1	3	category3	1	3
4	product4	4	category4	null	null
3	product3	4	category4	null	null
2	product2	4	category4	2	4
1	product1	4	category4	null	null
4	product4	5	category5	null	null
3	product3	5	category5	null	null
2	product2	5	category5	null	null
1	product1	5	category5	null	null