Magento 2 : Delete products having no name using sql or admin

Question

I uploaded a csv in which there were some products having no name, so it comes out blank in frontend.
I tried getting these products in catalog_product_entity_varchar but there were no NULL values in this table corresponding to attribute_id of Product Name attribute. Is there any way to delete these products using admin/sql query.

Answer 1

Get all products with empty name

SELECT main_tbl.* FROM catalog_product_entity main_tbl 
LEFT JOIN catalog_product_entity_varchar name_tbl ON main_tbl.entity_id = name_tbl.entity_id AND name_tbl.attribute_id = (
        SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product')
)
WHERE name_tbl.value IS NULL OR name_tbl.value = ''

Delete products with empty name

DELETE main_tbl FROM catalog_product_entity main_tbl 
LEFT JOIN catalog_product_entity_varchar name_tbl ON main_tbl.entity_id = name_tbl.entity_id AND name_tbl.attribute_id = (
        SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product')
)
WHERE name_tbl.value IS NULL OR name_tbl.value = '';