Magento 2 - Clean product EAV values (zombie product values, even if attribute(s) is/are not on concerned attribute set(s) anymore). Inspiration from https://github.com/magento/data-migration-tool/issues/598

m2_clean_product_eav_values.sql

CREATE TABLE catalog_product_entity_int_old LIKE catalog_product_entity_int;
INSERT INTO catalog_product_entity_int_old SELECT * FROM catalog_product_entity_int;

DELETE FROM catalog_product_entity_int
    WHERE value_id IN
        (SELECT cpei.value_id
            FROM catalog_product_entity_int_old cpei
            WHERE cpei.attribute_id NOT IN
                (SELECT eea.attribute_id
                    FROM eav_entity_attribute eea
                        JOIN catalog_product_entity cpe ON eea.attribute_set_id = cpe.attribute_set_id
                    WHERE cpe.row_id = cpei.row_id)
        ORDER BY cpei.row_id);
DROP TABLE catalog_product_entity_int_old;

CREATE TABLE catalog_product_entity_text_old LIKE catalog_product_entity_text;
INSERT INTO catalog_product_entity_text_old SELECT * FROM catalog_product_entity_text;

DELETE FROM catalog_product_entity_text
    WHERE value_id IN
        (SELECT cpei.value_id
            FROM catalog_product_entity_text_old cpei
            WHERE cpei.attribute_id NOT IN
                (SELECT eea.attribute_id
                    FROM eav_entity_attribute eea
                        JOIN catalog_product_entity cpe ON eea.attribute_set_id = cpe.attribute_set_id
                    WHERE cpe.row_id = cpei.row_id)
        ORDER BY cpei.row_id);
DROP TABLE catalog_product_entity_text_old;

CREATE TABLE catalog_product_entity_varchar_old LIKE catalog_product_entity_varchar;
INSERT INTO catalog_product_entity_varchar_old SELECT * FROM catalog_product_entity_varchar;

DELETE FROM catalog_product_entity_varchar
    WHERE value_id IN
        (SELECT cpei.value_id
            FROM catalog_product_entity_varchar_old cpei
            WHERE cpei.attribute_id NOT IN
                (SELECT eea.attribute_id
                    FROM eav_entity_attribute eea
                        JOIN catalog_product_entity cpe ON eea.attribute_set_id = cpe.attribute_set_id
                    WHERE cpe.row_id = cpei.row_id)
        ORDER BY cpei.row_id);
DROP TABLE catalog_product_entity_varchar_old;

CREATE TABLE catalog_product_entity_datetime_old LIKE catalog_product_entity_datetime;
INSERT INTO catalog_product_entity_datetime_old SELECT * FROM catalog_product_entity_datetime;

DELETE FROM catalog_product_entity_datetime
    WHERE value_id IN
        (SELECT cpei.value_id
            FROM catalog_product_entity_datetime_old cpei
            WHERE cpei.attribute_id NOT IN
                (SELECT eea.attribute_id
                    FROM eav_entity_attribute eea
                        JOIN catalog_product_entity cpe ON eea.attribute_set_id = cpe.attribute_set_id
                    WHERE cpe.row_id = cpei.row_id)
        ORDER BY cpei.row_id);
DROP TABLE catalog_product_entity_datetime_old;
        
CREATE TABLE catalog_product_entity_decimal_old LIKE catalog_product_entity_decimal;
INSERT INTO catalog_product_entity_decimal_old SELECT * FROM catalog_product_entity_decimal;

DELETE FROM catalog_product_entity_decimal
    WHERE value_id IN
        (SELECT cpei.value_id
            FROM catalog_product_entity_decimal_old cpei
            WHERE cpei.attribute_id NOT IN
                (SELECT eea.attribute_id
                    FROM eav_entity_attribute eea
                        JOIN catalog_product_entity cpe ON eea.attribute_set_id = cpe.attribute_set_id
                    WHERE cpe.row_id = cpei.row_id)
        ORDER BY cpei.row_id);
DROP TABLE catalog_product_entity_decimal_old;