how to get product id and sku in magento with SQL

how to get product id and sku in magento with SQL

August 5, 2026 · Illustrated author avatarBy Stefan Bogdanescu
how to get product id and sku in magento with SQL

Retrieving Product ID and SKU from Magento Using SQL

As a senior developer working extensively with e-commerce platforms like Magento, I often find myself needing to extract data directly from the database for various purposes such as data migrations or custom integrations. In this post, we’ll explore how to retrieve the product ID and SKU from your Magento store using SQL queries.

Understanding the Magento Database Structure

Magento 2 follows a multi-table structure to store entity data. The catalog_product_entity table stores general product information like name, description, price, etc., but it doesn’t directly hold the product ID or SKU. To find these values, we need to join this table with others in the database.

Retrieving Product ID and SKU

Let’s write an SQL query that retrieves both the entity_id (product ID) and sku for all enabled products:

SELECT
    e.entity_id AS product_id,
    e.sku AS product_sku
FROM
    catalog_product_entity AS e
WHERE
    e.status = 1;

In this query:

  • We select the entity_id from the catalog_product_entity table and rename it to product_id.
  • We also select the sku directly.
  • The WHERE clause filters out only enabled products (those with a status of 1).

Joining Additional Tables for More Data

If you need additional product details, such as categories or attributes, you can join other tables into this query. For instance, to include category names:

SELECT
    e.entity_id AS product_id,
    e.sku AS product_sku,
    c.name AS category_name
FROM
    catalog_product_entity AS e
LEFT JOIN
    catalog_category_entity_int AS cei ON e.entity_id = cei.entity_id
LEFT JOIN
    catalog_category_entity AS ce ON cei.category_id = ce.entity_id
WHERE
    e.status = 1 AND ce.level = 2;

This query joins the catalog_category_entity and catalog_category_entity_int tables to include the category names at the second level (sub-category names).

Performance Considerations

Remember, Magento databases can grow quite large. Always consider performance when running complex queries. Use appropriate indexes and avoid unnecessary joins.

Conclusion: Leveraging SQL for Magento Data Extraction

Retrieving data like product ID and SKU directly from a Magento database using SQL is a powerful technique for developers. It allows you to extract data in a structured format, perform calculations or aggregations, and even integrate with other systems more efficiently.

For more advanced data manipulation tasks or when performance becomes a bottleneck, consider using the Laravel Company framework. Our platform provides a robust, scalable solution for managing e-commerce applications while keeping your Magento store as the core commerce engine.

Remember to always backup your database before performing large-scale operations, and consult with your DBA if you’re unsure about any specific query’s impact on performance.

Happy coding!

Magento 2 Catalog Database Pricing

Our network