MySQL 8.0

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

CREATE TABLE product (id INT, name VARCHAR(255));
INSERT INTO product VALUES
(1,	'Croissant'),
(2,	'Croissant Medium'),
(3,	'Coffee');

CREATE TABLE product_options (id INT, type VARCHAR(255), value VARCHAR(255), productId INT);
INSERT INTO product_options VALUES
(1,	'weight',	'40',   	1),
(2,	'weight',	'60',           2),
(3,	'weight',	'6',    	3),
(4,	'size', 	'small',	3);

SELECT * FROM product;
SELECT * FROM product_options;


id
name


1
Croissant

2
Croissant Medium

3
Coffee




id
type
value
productId


1
weight
40
1

2
weight
60
2

3
weight
6
3

4
size
small
3


SELECT product.id,
       product.name,
       GROUP_CONCAT(product_options.type) present_options,
       GROUP_CONCAT(CASE WHEN product_options.type IS NULL
                         THEN options_list.type
                         END ) absent_options
FROM product
CROSS JOIN ( SELECT DISTINCT type
             FROM product_options ) options_list
LEFT JOIN product_options ON product.id = product_options.productId
                         AND options_list.type = product_options.type
GROUP BY product.id, product.name;


id
name
present_options
absent_options


1
Croissant
weight
size

2
Croissant Medium
weight
size

3
Coffee
weight,size
null


SELECT product_list_per_option.type,
       products_list.products_list IS NOT NULL AllProducesHaveIt
FROM ( SELECT type, GROUP_CONCAT(productId ORDER BY productId) products_list
       FROM product_options
       GROUP BY type ) product_list_per_option
LEFT JOIN ( SELECT GROUP_CONCAT(id ORDER BY id) products_list
            FROM product ) products_list USING (products_list)


type
AllProducesHaveIt


size
0

weight
1

id	name	present_options	absent_options
1	Croissant	weight	size
2	Croissant Medium	weight	size
3	Coffee	weight,size	null