Magento 2 - Retrieve some products data (name, price, stock qty, images, categories...) from SQL query. On Magento Commerce versions, you will have to replace `entity_id` by `row_id`.

m2_get_products_data.sql

SELECT e.entity_id AS 'id',
       v1.value AS 'name',
       e.sku,
       d1.value AS 'price',
       si.qty AS 'qty',
       t1.value AS 'short_description',
       t2.value AS 'description',
       v2.value AS 'image',
       v3.value AS 'thumbnail',
       mg.value AS 'media_gallery',
       cids.category_ids AS 'category_ids',
       cids.category_names AS 'category_names'
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
AND v1.store_id = 0
AND v1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'name'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t1 ON e.entity_id = t1.entity_id
AND t1.store_id = 0
AND t1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'short_description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t2 ON e.entity_id = t2.entity_id
AND t2.store_id = 0
AND t2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
AND v2.store_id = 0
AND v2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'image'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v3 ON e.entity_id = v3.entity_id
AND v3.store_id = 0
AND v3.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'thumbnail'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
AND d1.store_id = 0
AND d1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'price'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
  (SELECT m1.entity_id,
          GROUP_CONCAT(m2.value) AS value
   FROM catalog_product_entity_media_gallery_value_to_entity m1
   INNER JOIN catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id
   AND m2.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'media_gallery'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_product'))
   GROUP BY m1.entity_id) mg ON e.entity_id = mg.entity_id
LEFT JOIN
  (SELECT product_id,
          GROUP_CONCAT(c.category_id SEPARATOR ',') AS category_ids,
          GROUP_CONCAT(cv.value SEPARATOR ',') AS category_names
   FROM catalog_category_product c
   INNER JOIN catalog_category_entity_varchar cv ON c.category_id = cv.entity_id
   AND cv.store_id = 0
   AND cv.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'name'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_category'))
   GROUP BY product_id) cids ON e.entity_id = cids.product_id
LEFT JOIN cataloginventory_stock_item si ON e.entity_id = si.product_id

Awesome, I had to change the entity_id's to row_id in joins to match the structure of the Magento Commerce 2.4.2 on our instance but other than that works like a charm!

Yeah, indeed @WakizashiYoshikawa, I have just update gist description. Thx ;)

Here is the query for Magento Commerce 2.4x

SELECT e.entity_id AS 'id',
       v1.value AS 'name',
       e.sku,
       d1.value AS 'price',
       si.qty AS 'qty',
       t1.value AS 'short_description',
       t2.value AS 'description',
       v2.value AS 'image',
       v3.value AS 'thumbnail',
       mg.value AS 'media_gallery',
       cids.category_ids AS 'category_ids',
       cids.category_names AS 'category_names'
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.row_id
AND v1.store_id = 0
AND v1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'name'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t1 ON e.entity_id = t1.row_id
AND t1.store_id = 0
AND t1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'short_description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t2 ON e.entity_id = t2.row_id
AND t2.store_id = 0
AND t2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'description'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.row_id
AND v2.store_id = 0
AND v2.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'image'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v3 ON e.entity_id = v3.row_id
AND v3.store_id = 0
AND v3.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'thumbnail'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.row_id
AND d1.store_id = 0
AND d1.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'price'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
  (SELECT m1.row_id,
          GROUP_CONCAT(m2.value) AS value
   FROM catalog_product_entity_media_gallery_value_to_entity m1
   INNER JOIN catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id
   AND m2.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'media_gallery'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_product'))
   GROUP BY m1.row_id) mg ON e.entity_id = mg.row_id
LEFT JOIN
  (SELECT product_id,
          GROUP_CONCAT(c.category_id SEPARATOR ',') AS category_ids,
          GROUP_CONCAT(cv.value SEPARATOR ',') AS category_names
   FROM catalog_category_product c
   INNER JOIN catalog_category_entity_varchar cv ON c.category_id = cv.row_id
   AND cv.store_id = 0
   AND cv.attribute_id =
     (SELECT attribute_id
      FROM eav_attribute
      WHERE attribute_code = 'name'
        AND entity_type_id =
          (SELECT entity_type_id
           FROM eav_entity_type
           WHERE entity_type_code = 'catalog_category'))
   GROUP BY product_id) cids ON e.entity_id = cids.product_id
LEFT JOIN cataloginventory_stock_item si ON e.entity_id = si.product_id

limit 5;

I'm not sure if our local instance got messed up or what but relation e.entity_id = x.row_id returned completely wrong items. I had to make relation e.row_id = x.row_id (catalog_product_entity.row_id = catalog_product_entity_varchar.row_id). I am still investigating this on our local instance and implementation...

Also, keep in mind that you probably want a distinct on that query, because on EE, your items with Scheduled changes will return all scheduled changes so you will get 3 rows for one SKU; 1. original one, 2. begining of scheduled change, 3. end of scheduled change. So as I said, either distinct or WHERE catalog_product_entity.created_in = 1