How to get products name and sku and url key using sql query in magento 2?

Question

How to get products name and sku and url key using sql query in magento 2 ?

Answer 1

SQL QUERY (Magento 2.1 and earlier):

SELECT nametable.value, 
           nametable.store_id, 
           catalog_product_entity.sku 
FROM   `catalog_product_entity_varchar` AS nametable 
           LEFT JOIN catalog_product_entity 
                  ON nametable.entity_id = catalog_product_entity.entity_id 
WHERE  nametable.attribute_id = (SELECT attribute_id 
                                     FROM   `eav_attribute` 
                                     WHERE  `entity_type_id` = 4 
                                            AND `attribute_code` LIKE 'name') 

SQL QUERY (Magento 2.2 and later):

SELECT nametable.value, 
   nametable.store_id, 
   catalog_product_entity.sku 
FROM   `catalog_product_entity_varchar` AS nametable 
   LEFT JOIN catalog_product_entity 
          ON nametable.row_id = catalog_product_entity.row_id 
WHERE  nametable.attribute_id = (SELECT attribute_id 
                             FROM   `eav_attribute` 
                             WHERE  `entity_type_id` = 4 
                                    AND `attribute_code` LIKE 'name') 

As mentioned by Shashank Kumrawat, Sku value is stored in catalog_product_entity and name field value is stored in catalog_product_entity_varchar

As Magento support multi-store data, So for single SKU, multiple row can be exits.catalog_product_entity_varchar have store id field.If you >> want specific store name then just nametable.store_id = {StoreId}

Answer 2

You can use directly sql queries in magento2 as shown on this link -

using object manager (not recommended)

https://webkul.com/blog/magento2-write-custom-mysql-query/

Without object manager (recommended)

How to call Direct SQL Queries and join to collection In Magento2

To get SKU, you need to use table catalog_product_entity

and to get product name, need to use table catalog_product_entity_varchar

Answer 3

For Magento 2.2x versions, use row_id instead of entity_id in above sql queries