Retrieving Configurable Product Options and Values in Magento 2 Using SQL

In Magento 2, you can retrieve product information, including configurable product options and their values, via SQL queries. However, it’s important to note that using SQL queries directly can be complex, and it’s recommended to use Magento’s built-in models and collections for this purpose. Below is a simplified example of how you can fetch product information, including configurable options and their values, using SQL.

SELECT cpe.sku AS product_sku,  
       cpe.entity_id AS product_id,  
       cpsl.parent_id as row_id,  
       cpe.type_id as type,  
       cpsa.attribute_id as attribute_id,  
       ea.attribute_code as attribute_code,  
       eaov.value as option_value,  
       eaov.value_id as value_id,  
       eaov.option_id as option_id  
FROM catalog_product_entity AS cpe  
         JOIN catalog_product_super_link AS cpsl ON cpe.entity_id = cpsl.product_id  
  JOIN catalog_product_super_attribute AS cpsa ON cpsl.parent_id = cpsa.product_id  
  join eav_attribute as ea on ea.attribute_id = cpsa.attribute_id and ea.attribute_id = cpsa.attribute_id  
  join eav_attribute_option eao on ea.attribute_id = eao.attribute_id  
  join eav_attribute_option_value eaov on eao.option_id = eaov.option_id  
where cpsl.parent_id = [ROW_ID]

Replace [Your Parent ROW ID] with the ID of the configurable product for which you want to retrieve the associated simple products and their option values.

Please be cautious when using SQL queries directly, as they can potentially cause data integrity issues or other problems if not executed correctly. Always create backups and test SQL queries in a development or staging environment before applying them to a live store. Additionally, Magento’s database structure may change between versions, so ensure your SQL queries are compatible with your specific Magento 2 version. It’s often safer and more maintainable to use Magento’s API and models for data retrieval and manipulation.