Retrieve Product Details in Magento with SQL

Retrieve Product Details in Magento with SQL

October 8, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Retrieve Product Details in Magento with SQL

Retrieving Product Details in Magento Using SQL

As an e-commerce developer, I frequently work with clients who are looking for ways to analyze their product data stored in Magento’s database using tools like Tableau. One common challenge is retrieving relevant product attributes efficiently through SQL queries.

If you’re attempting to retrieve basic product details such as ID, SKU, cost, price, and special price for simple products, here’s a step-by-step guide to craft the appropriate SQL query:

Understanding Magento’s Database Schema

Magento uses Entity-Attribute-Value (EAV) tables to store custom product attributes. The core tables you’ll need to understand are:

  1. catalog_product_entity: Holds basic product information like ID, type, and status.
  2. catalog_product_entity_varchar: Stores text-based attribute values linked by the entity_id field.

Constructing the SQL Query

Here’s a structured approach to create an SQL query that retrieves the desired product details:

SELECT
    cpe.entity_id,
    cpe.sku,
    cat_cost.value AS cost,
    cat_price.value AS price,
    cat_special.value AS special_price
FROM
    catalog_product_entity AS cpe
LEFT JOIN
    catalog_product_entity_decimal AS cat_cost ON cpe.entity_id = cat_cost.entity_id AND cat_cost.attribute_id = (
        SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'cost'
    )
LEFT JOIN
    catalog_product_entity_decimal AS cat_price ON cpe.entity_id = cat_price.entity_id AND cat_price.attribute_id = (
        SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price'
    )
LEFT JOIN
    catalog_product_entity_varchar AS cat_special ON cpe.entity_id = cat_special.entity_id AND cat_special.attribute_id = (
        SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'special_price'
    )
WHERE
    cpe.type_id IN ('simple')

This query accomplishes the following:

  • Selects the required columns from the catalog_product_entity table.
  • Performs left joins with relevant EAV tables (catalog_product_entity_decimal and catalog_product_entity_varchar) to retrieve decimal and varchar attribute values, respectively.
  • Filters for simple product types using the WHERE clause.

Best Practices for Magento Database Queries

  1. Avoid Using Wildcards in Joins: Notice I’m using specific attribute_ids instead of wildcards like %. This ensures better performance by reducing the number of rows scanned during joins.

  2. Optimize Subqueries: The subqueries used to obtain attribute_id values could be optimized or moved outside the main query if needed, depending on your database setup and the size of your data.

Conclusion

Retrieving product details from a Magento database requires an understanding of the EAV schema. By following this structured approach, you can craft efficient SQL queries that retrieve the necessary information without pulling irrelevant data.

Remember to always backup your database before running complex queries, especially when dealing with joins and subqueries.

For more advanced techniques in working with Magento’s database, I recommend exploring the Laravel Company blog for detailed tutorials and best practices from experienced developers.

Catalog Database Pricing CLI

Our network