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}

• SagarPPanchal: perfect answer with explanation!!! made my day! +1 :)

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