Magento Adobe Commerce 2 SQL queries to fetch Product, Category, Customer, Order, Invoice Cart/Quote Data

Magento 2 or 1 and its ancestor Adobe Commerce has a really weird outdated database structure called EAV — entity attribute value. This architecture is against any software and database design best practices where one entity is one table. Magento has one entity data spreader between dozen of tables and it makes Magento almost not usable for software developers. You should be certified in Magento bad practices and use Magento's slow and bloated PHP framework to fetch the sample data from the database. However today we will demolition Magento down to data and raw database.

the Book “Linked Data Patterns” by Leigh Dodds and Ian Davis

A pattern catalog for modeling, publishing, and consuming Linked Data

describes Magento 2 Entity-Attribute-Value (EAV) database structure as “the declared old approach”

Linked Data Patterns

So, now best talents could use Magento with any framework and programming languages (GO, NodeJs, Python, Rust, Ruby, C++, and C#)not limited to PHP and outdated Zend Framework 1 and Laminas and KnockoutJS.

For many Magento developers, SQL is still a “low-level” computer language for data. It is considered an ancient and difficult-to-understand language designed for special secret engineers with special secret skills. In this way, a mere mortal should not write SQL queries and should rely on Magento ORM. And we are used to the fact that Magento ORM, Models, Repositories, and and Magento extensions generate tons of “garbage” queries against MySQL database, turning SQL into dumb bytecode. And we can basically just observe this process and hope for the best.

But SQL first of all was invented especially for humans, not for machines. So let’s treat SQL like a normal computer language.

Magenta uses separate attribute values tables for each data type (int, decimal, varchar256, text, blob etc.) and the metadata table (eav_attribute) for each attributes identifying the EAV table in which its data will be stored.

“EAV brings some aspects of normalization to the database table schema”. In a traditional database, tables have a fixed number of columns. It is like NoSQL or JsonB Mysql data but an outdated approach.

The following diagram of the catalog_product entirety shows the Magento EAV system:

• catalog_product_entity: determines entity containing attribute data (with attribute type as static) — It is similar to the product table.
• eav_attribute: is the table of attribute information.
• catalog_product_entity_datetime, catalog_product_entity_decimal, catalog_product_entity_int, catalog_product_entity_text, catalog_product_entity_varchar: includes attribute values — It is equivalent to attribute_value table.

Directly querying EAV data to analyze cohorts, funnels, and time series is tedious and challenging. These queries are often complex and quickly become unmaintainable. The best long-term solution is to flatten the EAV dataset out into a traditional ER model. This form of data is ultimately the most flexible and intuitive. After selecting a flattening pattern, we also need to figure out the best method to persist and update the flattened resultsets.

The simple RAW Queries are ->

Select Magento Category SQL Query:

SET @entity_id = 1;
(SELECT e.attribute_code, 'varchar' AS type, v.store_id, v.value FROM catalog_category_entity_varchar v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'datetime' AS type, v.store_id, v.value FROM catalog_category_entity_datetime v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'decimal' AS type, v.store_id, v.value FROM catalog_category_entity_decimal v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'int' AS type, v.store_id, v.value FROM catalog_category_entity_int v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'text' AS type, v.store_id, v.value FROM catalog_category_entity_text v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
ORDER BY attribute_code;

Select Magento Product SQL query:

SET @entity_id = 1;
(SELECT e.attribute_code, 'varchar' AS type, v.store_id, v.value FROM catalog_product_entity_varchar v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'datetime' AS type, v.store_id, v.value FROM catalog_product_entity_datetime v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'decimal' AS type, v.store_id, v.value FROM catalog_product_entity_decimal v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'int' AS type, v.store_id, v.value FROM catalog_product_entity_int v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'text' AS type, v.store_id, v.value FROM catalog_product_entity_text v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
ORDER BY attribute_code;

Flattening the Magento 2 EAV model

Several subselect or join techniques can turn the attribute tables into a single row for each entity. These techniques require you to know what attributes are wanted.

Where each join is related to a single attribute. 100 attributes = 100 joins, so avoid magento EAV junk. The complexity and execution time of this query are the same as those of other methods = N attributes * N products

 SELECT e.sku, price.value `price`, `short_description`.value `short_description`, `description`.value `description`, `name`.value `name`, `special_price`.value  `special_price`, `image`.value `image`, `gallery`.value `gallery`, `status`.value `status` from catalog_product_entity as e 
 left join catalog_product_entity_datetime as a1 on (e.row_id = a1.row_id and a1.attribute_id = 1 and a1.store_id = 0)
 left join catalog_product_entity_decimal as `price` on (e.row_id = price.row_id and price.attribute_id = 77 and price.store_id = 0)
 left join catalog_product_entity_int as `status` on (e.row_id = `status`.row_id and `status`.attribute_id = 97 and price.store_id = 0)
 left join catalog_product_entity_gallery as a3 on (e.row_id = a3.row_id and a3.attribute_id = 1 and a3.store_id = 0)
 left join catalog_product_entity_int as a4 on (e.row_id = a4.row_id and a4.attribute_id = 1 and a4.store_id = 0)
 left join catalog_product_entity_text as `description` on (e.row_id = `description`.row_id and `description`.attribute_id = 75 and `description`.store_id = 0)
 left join catalog_product_entity_text as `short_description` on (e.row_id = `short_description`.row_id and `short_description`.attribute_id = 76 and `short_description`.store_id = 0)
 left join catalog_product_entity_varchar as `name` on (e.row_id = `name`.row_id and `name`.attribute_id = 73 and `name`.store_id = 0)
 left join catalog_product_entity_decimal as `special_price` on (e.row_id = `special_price`.row_id and `special_price`.attribute_id = 78 and `special_price`.store_id = 0)
 left join catalog_product_entity_varchar as `image` on (e.row_id = `image`.row_id and `image`.attribute_id = 88 and `image`.store_id = 0)
 left join catalog_product_entity_varchar as `gallery` on (e.row_id = `gallery`.row_id and `gallery`.attribute_id = 96 and `gallery`.store_id = 0) where e.sku  is not NULL;

where attribute ids come from :

select * from eav_attribute as `a` where entity_type_id = 4 and attribute_code  in ('name','status', 'special_price','price','description','short_description','small_image')

It is important for the database to scan only a single record on the EAV table for a single attribute for a single entity. This is the key to performance.

It is important to look at the vast numbers of modern technologies for sparse data. Modern row store databases today are developing native handling of unstructured or sparse matrices. MySQL has native support if JSON data fields.

Also, you can use subquery but I don’t really like this approach and I don’t have a working example but I have structure :

SELECT
product_entity
,(SELECT value
    FROM decimal
    WHERE product_entity.row_id = decimal.row_id
    AND attribute = "fattribute_name1"
) as first_name
, (SELECT value
    FROM text
    WHERE product_entity.row_id = text.row_id
    AND attribute = "fattribute_name2"
) as first_name
...

EAV tables are a poor design choice and should only be used if you cannot know in advance what fields are needed. Fields such as firstname can be known that you will need them in advance and should be in proper related tables. If you want to use EAV for everything, at least stop using a relational database and use a NOSQL database designed for this.

To get the result you want you will need to join to the EAV tables once for each type of data you want.

Select Magento Customer SQL query

SET @entity_id = 1;
(SELECT e.attribute_code, 'varchar' AS type, v.store_id, v.value FROM customer_entity_varchar v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'datetime' AS type, v.store_id, v.value FROM customer_entity_datetime v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'decimal' AS type, v.store_id, v.value FROM customer_entity_decimal v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'int' AS type, v.store_id, v.value FROM customer_entity_int v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'text' AS type, v.store_id, v.value FROM customer_entity_text v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
ORDER BY attribute_code;

Select Customer Address:

SET @entity_id = 1;
(SELECT e.attribute_code, 'varchar' AS type, v.store_id, v.value FROM customer_address_entity_varchar v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'datetime' AS type, v.store_id, v.value FROM customer_address_entity_datetime v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'decimal' AS type, v.store_id, v.value FROM customer_address_entity_decimal v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'int' AS type, v.store_id, v.value FROM customer_address_entity_int v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
UNION
(SELECT e.attribute_code, 'text' AS type, v.store_id, v.value FROM customer_address_entity_text v
JOIN eav_attribute e ON e.attribute_id = v.attribute_id WHERE v.entity_id = @entity_id)
ORDER BY attribute_code;

However, not everything is so easy. Magento has different attributes values for different scopes (store_view, web_sites). Also in the Enterprise/commerce version, the data is more broken and it uses ROW_Id instead of entity_Id column as identifier where the same entity can have different values in different versions. That's why I’m not recommending using that paid version it is expensive and has more bugs.

the original Magento SQL query generated by outdated Magento Zend 1 DB ORM is:

explain SELECT `u`.* FROM ((
SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` 
FROM `catalog_product_entity_varchar` AS `t` 
WHERE (entity_id = ‘9244’) AND (attribute_id IN ([]) AND (`store_id` IN (‘1’, 0)))
UNION ALL(
SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` 
FROM `catalog_product_entity_text` AS `t` 
WHERE (entity_id = ‘9244’) AND (attribute_id IN ([])) AND (`store_id` IN (‘1’, 0)))
UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_decimal` AS `t` 
WHERE (entity_id = ‘9244’) AND (attribute_id IN
([])) AND (`store_id` IN (‘1’, 0)))
UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_int` AS `t` 
WHERE (entity_id = ‘9244’) AND (attribute_id IN ([])) AND (`store_id` IN (‘1’, 0)))
UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_datetime` AS `t` 
WHERE (entity_id = ‘9244’) AND (attribute_id IN ([])) AND (`store_id` IN (‘1’, 0))
)) AS `u` ORDER BY `store_id` ASC \G

So you need to add store_id and filter with the fallback to default store 0 and required attributes_ids:

## Store_ids
`store_id` IN (‘1’, 0)
## Attribute_ids
attribute_id IN ([])

However, as Magento uses outdated PHP technology it is not the best way to fetch the data. For the I/o nonblocking languages like Go, Python, NodeJs, Rust it is better to use parallel threads to fetch the data asynchronously for better performance. For multi-processor DB each query will be resolved by different CPUs and will be N times faster…

MAgento 2 SQL fetch Order:

SELECT oredrs.* FROM sales_flat_order oredrs WHERE oredrs.increment_id = '00000001';

As we can see Order in the Magento is the only normal Magento table when the rest of the framework is uses bad software development practices.

the advanced query to fetch proper full data is :

SQL Query to fetch Magento EAV data for Category:

SET @entityid = '1'; 
SELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'
FROM catalog_category_entity e 
JOIN catalog_category_entity_varchar eav  
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNIONSELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
FROM catalog_category_entity e
JOIN catalog_category_entity_int eav  
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNIONSELECT ea.attribute_id, ea.attribute_code, eav.value 
AS 'value', 'decimal' AS 'type'
FROM catalog_category_entity e
JOIN catalog_category_entity_decimal eav  
ON e.entity_id = eav.entity_idJOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNIONSELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'FROM catalog_category_entity e
JOIN catalog_category_entity_datetime eav  
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNIONSELECT ea.attribute_id, ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
FROM catalog_category_entity e
JOIN catalog_category_entity_text eav  
ON e.entity_id = eav.entity_idJOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid

or with the attribute data from the (eav_attribute)

SET @entitySku = 'my-sku';
SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type', eaov.value AS 'option_frontend_label' 
FROM catalog_product_entity e  
JOIN catalog_product_entity_varchar eav 
ON e.entity_id = eav.entity_id  
JOIN eav_attribute ea 
ON eav.attribute_id = ea.attribute_id  
LEFT JOIN eav_attribute_option eao ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)  
LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id
WHERE e.sku = @entitySku
UNION(  
SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'int' AS 'type', eaov.value AS 'option_frontend_label'  
FROM catalog_product_entity e    
JOIN catalog_product_entity_int eav 
ON e.entity_id = eav.entity_id    
JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id    
LEFT JOIN eav_attribute_option eao 
ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)    
LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id  
WHERE e.sku = @entitySku)
UNION(
SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type', eaov.value AS 'option_frontend_label'  
FROM catalog_product_entity e    
JOIN catalog_product_entity_decimal eav 
ON e.entity_id = eav.entity_id    
JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id    
LEFT JOIN eav_attribute_option eao 
ON (ea.attribute_id = eao.attribute_id 
AND eav.value = eao.option_id)    
LEFT JOIN eav_attribute_option_value eaov 
ON eao.option_id = eaov.option_id  
WHERE e.sku = @entitySku)
UNION(  
SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type', eaov.value AS 'option_frontend_label'  
FROM catalog_product_entity e    
JOIN catalog_product_entity_datetime eav ON e.entity_id = eav.entity_id    
JOIN eav_attribute ea ON eav.attribute_id = ea.attribute_id    
LEFT JOIN eav_attribute_option eao 
ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)    
LEFT JOIN eav_attribute_option_value eaov 
ON eao.option_id = eaov.option_id  WHERE e.sku = @entitySku)
UNION(  
SELECT ea.`attribute_id`, ea.attribute_code, eav.value AS 'value', 'text' AS 'type', eaov.value AS 'option_frontend_label'  
FROM catalog_product_entity e    
JOIN catalog_product_entity_text eav 
ON e.entity_id = eav.entity_id    
JOIN eav_attribute ea 
ON eav.attribute_id = ea.attribute_id    
LEFT JOIN eav_attribute_option eao
ON (ea.attribute_id = eao.attribute_id AND eav.value = eao.option_id)    
LEFT JOIN eav_attribute_option_value eaov ON eao.option_id = eaov.option_id  WHERE e.sku = @entitySku);

SQL Query to fetch Magento EAV data for Products:

SET @entityid = 3; 
SELECT ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_varchar eav  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNIONSELECT ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_int eav  
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNIONSELECT ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type'FROM catalog_product_entity e
JOIN catalog_product_entity_decimal eav  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNIONSELECT ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_datetime eav  
ON e.entity_id = eav.entity_idJOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNIONSELECT ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_text eav  
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid

Query to Query MAgento Customer Data:

SET @customerid = (SELECT e.entity_id     FROM customer_entity e    WHERE e.email = 'customer@example.com'); 
SELECT ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'FROM customer_entity e
JOIN customer_entity_varchar eav  
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
FROM customer_entity e
JOIN customer_entity_int eav  
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type'FROM customer_entity e
JOIN customer_entity_decimal eav 
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid
UNIONSELECT ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'FROM customer_entity e
JOIN customer_entity_datetime eav  
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid
UNIONSELECT ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
FROM customer_entity e
JOIN customer_entity_text eav  
ON e.entity_id = eav.entity_id
JOIN eav_attribute ea  
ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @customerid

SQL Query to fetch Product Data with the Join

SELECT `CatalogProductEntity`.*, `CatalogProductEntityVarchars`.`value_id` AS `CatalogProductEntityVarchars.value_id`, `CatalogProductEntityVarchars`.`attribute_id` AS `CatalogProductEntityVarchars.attribute_id`, `CatalogProductEntityVarchars`.`store_id` AS `CatalogProductEntityVarchars.store_id`, `CatalogProductEntityVarchars`.`entity_id` AS `CatalogProductEntityVarchars.entity_id`, `CatalogProductEntityVarchars`.`value` AS `CatalogProductEntityVarchars.value`, `CatalogProductEntityInts`.`value_id` AS `CatalogProductEntityInts.value_id`, `CatalogProductEntityInts`.`attribute_id` AS `CatalogProductEntityInts.attribute_id`, `CatalogProductEntityInts`.`store_id` AS `CatalogProductEntityInts.store_id`, `CatalogProductEntityInts`.`entity_id` AS `CatalogProductEntityInts.entity_id`, `CatalogProductEntityInts`.`value` AS `CatalogProductEntityInts.value`, `CatalogProductEntityTexts`.`value_id` AS `CatalogProductEntityTexts.value_id`, `CatalogProductEntityTexts`.`attribute_id` AS `CatalogProductEntityTexts.attribute_id`, `CatalogProductEntityTexts`.`store_id` AS `CatalogProductEntityTexts.store_id`, `CatalogProductEntityTexts`.`entity_id` AS `CatalogProductEntityTexts.entity_id`, `CatalogProductEntityTexts`.`value` AS `CatalogProductEntityTexts.value`, `CatalogProductEntityDecimals`.`value_id` AS `CatalogProductEntityDecimals.value_id`, `CatalogProductEntityDecimals`.`attribute_id` AS `CatalogProductEntityDecimals.attribute_id`, `CatalogProductEntityDecimals`.`store_id` AS `CatalogProductEntityDecimals.store_id`, `CatalogProductEntityDecimals`.`entity_id` AS `CatalogProductEntityDecimals.entity_id`, `CatalogProductEntityDecimals`.`value` AS `CatalogProductEntityDecimals.value`, `CatalogProductEntityDatetimes`.`value_id` AS `CatalogProductEntityDatetimes.value_id`, `CatalogProductEntityDatetimes`.`attribute_id` AS `CatalogProductEntityDatetimes.attribute_id`, `CatalogProductEntityDatetimes`.`store_id` AS `CatalogProductEntityDatetimes.store_id`, `CatalogProductEntityDatetimes`.`entity_id` AS `CatalogProductEntityDatetimes.entity_id`, `CatalogProductEntityDatetimes`.`value` AS `CatalogProductEntityDatetimes.value` 
FROM (SELECT `CatalogProductEntity`.`entity_id`, `CatalogProductEntity`.`attribute_set_id`, `CatalogProductEntity`.`type_id`, `CatalogProductEntity`.`sku`, `CatalogProductEntity`.`has_options`, `CatalogProductEntity`.`required_options`, `CatalogProductEntity`.`created_at`, `CatalogProductEntity`.`updated_at` FROM `catalog_product_entity` AS `CatalogProductEntity` WHERE `CatalogProductEntity`.`sku` = '24-MB01' LIMIT 1) AS `CatalogProductEntity` 
LEFT OUTER JOIN `catalog_product_entity_varchar` AS `CatalogProductEntityVarchars` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityVarchars`.`entity_id` 
LEFT OUTER JOIN `catalog_product_entity_int` AS `CatalogProductEntityInts` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityInts`.`entity_id` 
LEFT OUTER JOIN `catalog_product_entity_text` AS `CatalogProductEntityTexts` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityTexts`.`entity_id` 
LEFT OUTER JOIN `catalog_product_entity_decimal` AS `CatalogProductEntityDecimals` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityDecimals`.`entity_id` 
LEFT OUTER JOIN `catalog_product_entity_datetime` AS `CatalogProductEntityDatetimes` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityDatetimes`.`entity_id`;

The Query provided by incredible NodeJS Magento ORM (NodeJento):

GitHub - Genaker/nodejento: NodeJS implementation of the Magento 2 ORM data access layer without…

With the best execution plan:

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: CatalogProductEntityVarchars
type: ref
possible_keys: CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key: CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 4
ref: CatalogProductEntity.entity_id
rows: 3
Extra:
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: CatalogProductEntityInts
type: ref
possible_keys: CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key: CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 4
ref: CatalogProductEntity.entity_id
rows: 3
Extra:
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: CatalogProductEntityTexts
type: ref
possible_keys: CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key: CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 4
ref: CatalogProductEntity.entity_id
rows: 1
Extra:
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: CatalogProductEntityDecimals
type: ref
possible_keys: CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key: CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 4
ref: CatalogProductEntity.entity_id
rows: 1
Extra:
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: CatalogProductEntityDatetimes
type: ref
possible_keys: CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key: CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 4
ref: CatalogProductEntity.entity_id
rows: 1
Extra:
*************************** 7. row ***************************
id: 2
select_type: DERIVED
table: CatalogProductEntity
type: ref
possible_keys: CATALOG_PRODUCT_ENTITY_SKU
key: CATALOG_PRODUCT_ENTITY_SKU
key_len: 195
ref: const
rows: 1
Extra: Using index condition

or with limited attributes visible on the frontend fetched by the query (select attribute_id from catalog_eav_attribute where is_visible_on_front = 1 or is_html_allowed_on_front = 1 or is_visible = 1) and store_id in (0,1):

SELECT `CatalogProductEntity`.*, `CatalogProductEntityVarchars`.`value_id` AS `CatalogProductEntityVarchars.value_id`, `CatalogProductEntityVarchars`.`attribute_id` AS `CatalogProductEntityVarchars.attribute_id`, `CatalogProductEntityVarchars`.`store_id` AS `CatalogProductEntityVarchars.store_id`, `CatalogProductEntityVarchars`.`entity_id` AS `CatalogProductEntityVarchars.entity_id`, `CatalogProductEntityVarchars`.`value` AS `CatalogProductEntityVarchars.value`, `CatalogProductEntityInts`.`value_id` AS `CatalogProductEntityInts.value_id`, `CatalogProductEntityInts`.`attribute_id` AS `CatalogProductEntityInts.attribute_id`, `CatalogProductEntityInts`.`store_id` AS `CatalogProductEntityInts.store_id`, `CatalogProductEntityInts`.`entity_id` AS `CatalogProductEntityInts.entity_id`, `CatalogProductEntityInts`.`value` AS `CatalogProductEntityInts.value`, `CatalogProductEntityTexts`.`value_id` AS `CatalogProductEntityTexts.value_id`, `CatalogProductEntityTexts`.`attribute_id` AS `CatalogProductEntityTexts.attribute_id`, `CatalogProductEntityTexts`.`store_id` AS `CatalogProductEntityTexts.store_id`, `CatalogProductEntityTexts`.`entity_id` AS `CatalogProductEntityTexts.entity_id`, `CatalogProductEntityTexts`.`value` AS `CatalogProductEntityTexts.value`, `CatalogProductEntityDecimals`.`value_id` AS `CatalogProductEntityDecimals.value_id`, `CatalogProductEntityDecimals`.`attribute_id` AS `CatalogProductEntityDecimals.attribute_id`, `CatalogProductEntityDecimals`.`store_id` AS `CatalogProductEntityDecimals.store_id`, `CatalogProductEntityDecimals`.`entity_id` AS `CatalogProductEntityDecimals.entity_id`, `CatalogProductEntityDecimals`.`value` AS `CatalogProductEntityDecimals.value`, `CatalogProductEntityDatetimes`.`value_id` AS `CatalogProductEntityDatetimes.value_id`, `CatalogProductEntityDatetimes`.`attribute_id` AS `CatalogProductEntityDatetimes.attribute_id`, `CatalogProductEntityDatetimes`.`store_id` AS `CatalogProductEntityDatetimes.store_id`, `CatalogProductEntityDatetimes`.`entity_id` AS `CatalogProductEntityDatetimes.entity_id`, `CatalogProductEntityDatetimes`.`value` AS `CatalogProductEntityDatetimes.value` FROM (SELECT `CatalogProductEntity`.`entity_id`, `CatalogProductEntity`.`attribute_set_id`, `CatalogProductEntity`.`type_id`, `CatalogProductEntity`.`sku`, `CatalogProductEntity`.`has_options`, `CatalogProductEntity`.`required_options`, `CatalogProductEntity`.`created_at`, `CatalogProductEntity`.`updated_at` 
FROM `catalog_product_entity` AS `CatalogProductEntity`
 WHERE `CatalogProductEntity`.`sku` = '24-MB01' LIMIT 1) AS `CatalogProductEntity` 
LEFT OUTER JOIN `catalog_product_entity_varchar` AS `CatalogProductEntityVarchars` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityVarchars`.`entity_id` AND `CatalogProductEntityVarchars`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) 
AND `CatalogProductEntityVarchars`.`store_id` IN (0, 1) 
LEFT OUTER JOIN `catalog_product_entity_int` AS `CatalogProductEntityInts` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityInts`.`entity_id` AND `CatalogProductEntityInts`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityInts`.`store_id` IN (0, 1) 
LEFT OUTER JOIN `catalog_product_entity_text` AS `CatalogProductEntityTexts` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityTexts`.`entity_id` AND `CatalogProductEntityTexts`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityTexts`.`store_id` IN (0, 1) 
LEFT OUTER JOIN `catalog_product_entity_decimal` AS `CatalogProductEntityDecimals` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityDecimals`.`entity_id` AND `CatalogProductEntityDecimals`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityDecimals`.`store_id` IN (0, 1) 
LEFT OUTER JOIN `catalog_product_entity_datetime` AS `CatalogProductEntityDatetimes` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityDatetimes`.`entity_id` AND `CatalogProductEntityDatetimes`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityDatetimes`.`store_id` IN (0, 1);

Select Attributes Gallary and Stock, Tier Price Data SQL query:

SELECT `CatalogProductEntity`.`entity_id`, `CatalogProductEntity`.`attribute_set_id`, `CatalogProductEntity`.`type_id`, `CatalogProductEntity`.`sku`, `CatalogProductEntity`.`has_options`, `CatalogProductEntity`.`required_options`, `CatalogProductEntity`.`created_at`, `CatalogProductEntity`.`updated_at`, `CatalogProductEntityVarchars`.`value_id` AS `CatalogProductEntityVarchars.value_id`, `CatalogProductEntityVarchars`.`attribute_id` AS `CatalogProductEntityVarchars.attribute_id`, `CatalogProductEntityVarchars`.`store_id` AS `CatalogProductEntityVarchars.store_id`, `CatalogProductEntityVarchars`.`entity_id` AS `CatalogProductEntityVarchars.entity_id`, `CatalogProductEntityVarchars`.`value` AS `CatalogProductEntityVarchars.value`, `CatalogProductEntityInts`.`value_id` AS `CatalogProductEntityInts.value_id`, `CatalogProductEntityInts`.`attribute_id` AS `CatalogProductEntityInts.attribute_id`, `CatalogProductEntityInts`.`store_id` AS `CatalogProductEntityInts.store_id`, `CatalogProductEntityInts`.`entity_id` AS `CatalogProductEntityInts.entity_id`, `CatalogProductEntityInts`.`value` AS `CatalogProductEntityInts.value`, `CatalogProductEntityTexts`.`value_id` AS `CatalogProductEntityTexts.value_id`, `CatalogProductEntityTexts`.`attribute_id` AS `CatalogProductEntityTexts.attribute_id`, `CatalogProductEntityTexts`.`store_id` AS `CatalogProductEntityTexts.store_id`, `CatalogProductEntityTexts`.`entity_id` AS `CatalogProductEntityTexts.entity_id`, `CatalogProductEntityTexts`.`value` AS `CatalogProductEntityTexts.value`, `CatalogProductEntityDecimals`.`value_id` AS `CatalogProductEntityDecimals.value_id`, `CatalogProductEntityDecimals`.`attribute_id` AS `CatalogProductEntityDecimals.attribute_id`, `CatalogProductEntityDecimals`.`store_id` AS `CatalogProductEntityDecimals.store_id`, `CatalogProductEntityDecimals`.`entity_id` AS `CatalogProductEntityDecimals.entity_id`, `CatalogProductEntityDecimals`.`value` AS `CatalogProductEntityDecimals.value`, `CatalogProductEntityDatetimes`.`value_id` AS `CatalogProductEntityDatetimes.value_id`, `CatalogProductEntityDatetimes`.`attribute_id` AS `CatalogProductEntityDatetimes.attribute_id`, `CatalogProductEntityDatetimes`.`store_id` AS `CatalogProductEntityDatetimes.store_id`, `CatalogProductEntityDatetimes`.`entity_id` AS `CatalogProductEntityDatetimes.entity_id`, `CatalogProductEntityDatetimes`.`value` AS `CatalogProductEntityDatetimes.value`, `CatalogProductEntityMediaGalleries`.`value_id` AS `CatalogProductEntityMediaGalleries.value_id`, `CatalogProductEntityMediaGalleries`.`attribute_id` AS `CatalogProductEntityMediaGalleries.attribute_id`, `CatalogProductEntityMediaGalleries`.`value` AS `CatalogProductEntityMediaGalleries.value`, `CatalogProductEntityMediaGalleries`.`media_type` AS `CatalogProductEntityMediaGalleries.media_type`, `CatalogProductEntityMediaGalleries`.`disabled` AS `CatalogProductEntityMediaGalleries.disabled`, `CatalogProductEntityMediaGalleries->CatalogProductEntityMediaGalleryValueToEntity`.`value_id` AS `CatalogProductEntityMediaGalleries.CatalogProductEntityMediaGalleryValueToEntity.value_id`, `CatalogProductEntityMediaGalleries->CatalogProductEntityMediaGalleryValueToEntity`.`entity_id` AS `CatalogProductEntityMediaGalleries.CatalogProductEntityMediaGalleryValueToEntity.entity_id`, `CataloginventoryStockItems`.`item_id` AS `CataloginventoryStockItems.item_id`, `CataloginventoryStockItems`.`product_id` AS `CataloginventoryStockItems.product_id`, `CataloginventoryStockItems`.`stock_id` AS `CataloginventoryStockItems.stock_id`, `CataloginventoryStockItems`.`qty` AS `CataloginventoryStockItems.qty`, `CataloginventoryStockItems`.`min_qty` AS `CataloginventoryStockItems.min_qty`, `CataloginventoryStockItems`.`use_config_min_qty` AS `CataloginventoryStockItems.use_config_min_qty`, `CataloginventoryStockItems`.`is_qty_decimal` AS `CataloginventoryStockItems.is_qty_decimal`, `CataloginventoryStockItems`.`backorders` AS `CataloginventoryStockItems.backorders`, `CataloginventoryStockItems`.`use_config_backorders` AS `CataloginventoryStockItems.use_config_backorders`, `CataloginventoryStockItems`.`min_sale_qty` AS `CataloginventoryStockItems.min_sale_qty`, `CataloginventoryStockItems`.`use_config_min_sale_qty` AS `CataloginventoryStockItems.use_config_min_sale_qty`, `CataloginventoryStockItems`.`max_sale_qty` AS `CataloginventoryStockItems.max_sale_qty`, `CataloginventoryStockItems`.`use_config_max_sale_qty` AS `CataloginventoryStockItems.use_config_max_sale_qty`, `CataloginventoryStockItems`.`is_in_stock` AS `CataloginventoryStockItems.is_in_stock`, `CataloginventoryStockItems`.`low_stock_date` AS `CataloginventoryStockItems.low_stock_date`, `CataloginventoryStockItems`.`notify_stock_qty` AS `CataloginventoryStockItems.notify_stock_qty`, `CataloginventoryStockItems`.`use_config_notify_stock_qty` AS `CataloginventoryStockItems.use_config_notify_stock_qty`, `CataloginventoryStockItems`.`manage_stock` AS `CataloginventoryStockItems.manage_stock`, `CataloginventoryStockItems`.`use_config_manage_stock` AS `CataloginventoryStockItems.use_config_manage_stock`, `CataloginventoryStockItems`.`stock_status_changed_auto` AS `CataloginventoryStockItems.stock_status_changed_auto`, `CataloginventoryStockItems`.`use_config_qty_increments` AS `CataloginventoryStockItems.use_config_qty_increments`, `CataloginventoryStockItems`.`qty_increments` AS `CataloginventoryStockItems.qty_increments`, `CataloginventoryStockItems`.`use_config_enable_qty_inc` AS `CataloginventoryStockItems.use_config_enable_qty_inc`, `CataloginventoryStockItems`.`enable_qty_increments` AS `CataloginventoryStockItems.enable_qty_increments`, `CataloginventoryStockItems`.`is_decimal_divided` AS `CataloginventoryStockItems.is_decimal_divided`, `CataloginventoryStockItems`.`website_id` AS `CataloginventoryStockItems.website_id`, `CatalogProductEntityTierPrices`.`value_id` AS `CatalogProductEntityTierPrices.value_id`, `CatalogProductEntityTierPrices`.`entity_id` AS `CatalogProductEntityTierPrices.entity_id`, `CatalogProductEntityTierPrices`.`all_groups` AS `CatalogProductEntityTierPrices.all_groups`, `CatalogProductEntityTierPrices`.`customer_group_id` AS `CatalogProductEntityTierPrices.customer_group_id`, `CatalogProductEntityTierPrices`.`qty` AS `CatalogProductEntityTierPrices.qty`, `CatalogProductEntityTierPrices`.`value` AS `CatalogProductEntityTierPrices.value`, `CatalogProductEntityTierPrices`.`website_id` AS `CatalogProductEntityTierPrices.website_id`, `CatalogProductEntityTierPrices`.`percentage_value` AS `CatalogProductEntityTierPrices.percentage_value` FROM `catalog_product_entity` AS `CatalogProductEntity` 
LEFT OUTER JOIN `catalog_product_entity_varchar` AS `CatalogProductEntityVarchars` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityVarchars`.`entity_id` AND `CatalogProductEntityVarchars`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityVarchars`.`store_id` IN (0, 1) 
LEFT OUTER JOIN `catalog_product_entity_int` AS `CatalogProductEntityInts` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityInts`.`entity_id` AND `CatalogProductEntityInts`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityInts`.`store_id` IN (0, 1) 
LEFT OUTER JOIN `catalog_product_entity_text` AS `CatalogProductEntityTexts` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityTexts`.`entity_id` AND `CatalogProductEntityTexts`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityTexts`.`store_id` IN (0, 1) 
LEFT OUTER JOIN `catalog_product_entity_decimal` AS `CatalogProductEntityDecimals` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityDecimals`.`entity_id` AND `CatalogProductEntityDecimals`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityDecimals`.`store_id` IN (0, 1) 
LEFT OUTER JOIN `catalog_product_entity_datetime` AS `CatalogProductEntityDatetimes` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityDatetimes`.`entity_id` AND `CatalogProductEntityDatetimes`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityDatetimes`.`store_id` IN (0, 1) 
LEFT OUTER JOIN ( `catalog_product_entity_media_gallery_value_to_entity` AS `CatalogProductEntityMediaGalleries->CatalogProductEntityMediaGalleryValueToEntity` 
INNER JOIN `catalog_product_entity_media_gallery` AS `CatalogProductEntityMediaGalleries` ON `CatalogProductEntityMediaGalleries`.`value_id` = `CatalogProductEntityMediaGalleries->CatalogProductEntityMediaGalleryValueToEntity`.`value_id`) ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityMediaGalleries->CatalogProductEntityMediaGalleryValueToEntity`.`entity_id` 
LEFT OUTER JOIN `cataloginventory_stock_item` AS `CataloginventoryStockItems` ON `CatalogProductEntity`.`entity_id` = `CataloginventoryStockItems`.`product_id` 
LEFT OUTER JOIN `catalog_product_entity_tier_price` AS `CatalogProductEntityTierPrices` ON `CatalogProductEntity`.`entity_id` = `CatalogProductEntityTierPrices`.`entity_id` 
WHERE `CatalogProductEntity`.`sku` IN ('24-MB01', '24-MB04', '24-WG084', '24-WG085');

However, it is not efficient. It is better than the core Magento 2 broken framework but not ideal. The result will have too many rows with the NULL values. It is better to decouple one query with the joins with the multiple WHERE IN() queries and merge the data programmatically or using modern Python or NodeJS ORM.

SELECT `CatalogProductEntity`.`entity_id`, `CatalogProductEntity`.`attribute_set_id`, `CatalogProductEntity`.`type_id`, `CatalogProductEntity`.`sku`, `CatalogProductEntity`.`has_options`, `CatalogProductEntity`.`required_options`, `CatalogProductEntity`.`created_at`, `CatalogProductEntity`.`updated_at`
WHERE `CatalogProductEntity`.`sku` IN ('24-MB01', '24-MB04', '24-WG084', '24-WG085');

Separate SQL queries to fetch the attributes:

Varchars

SELECT `value_id`, `store_id`, `value`, `attribute_id`, `entity_id` FROM `catalog_product_entity_varchar` AS `CatalogProductEntityVarchar` WHERE (`CatalogProductEntityVarchar`.`entity_id` IN (57, 58, 77, 89) AND (`CatalogProductEntityVarchar`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityVarchar`.`store_id` IN (0, 1)));

Integers

SELECT `value_id`, `store_id`, `value`, `attribute_id`, `entity_id` FROM `catalog_product_entity_int` AS `CatalogProductEntityInt` WHERE (`CatalogProductEntityInt`.`entity_id` IN (57, 58, 77, 89) AND (`CatalogProductEntityInt`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityInt`.`store_id` IN (0, 1)));

Texts

SELECT `value_id`, `store_id`, `value`, `attribute_id`, `entity_id` FROM `catalog_product_entity_text` AS `CatalogProductEntityText` WHERE (`CatalogProductEntityText`.`entity_id` IN (57, 58, 77, 89) AND (`CatalogProductEntityText`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityText`.`store_id` IN (0, 1)));

Decimals

SELECT `value_id`, `store_id`, `value`, `attribute_id`, `entity_id` FROM `catalog_product_entity_decimal` AS `CatalogProductEntityDecimal` WHERE (`CatalogProductEntityDecimal`.`entity_id` IN (57, 58, 77, 89) AND (`CatalogProductEntityDecimal`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityDecimal`.`store_id` IN (0, 1)));

Datetimes

SELECT `value_id`, `store_id`, `value`, `attribute_id`, `entity_id` FROM `catalog_product_entity_datetime` AS `CatalogProductEntityDatetime` WHERE (`CatalogProductEntityDatetime`.`entity_id` IN (57, 58, 77, 89) AND (`CatalogProductEntityDatetime`.`attribute_id` IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 60, 61, 62, 63, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92, 93, 94, 95, 96, 97, 99, 100, 101, 102, 104, 105, 106, 114, 115, 116, 117, 118, 119, 120, 121, 123, 129, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164) AND `CatalogProductEntityDatetime`.`store_id` IN (0, 1)));

Fetch Stock Data

SELECT `item_id`, `product_id`, `stock_id`, `qty`, `min_qty`, `use_config_min_qty`, `is_qty_decimal`, `backorders`, `use_config_backorders`, `min_sale_qty`, `use_config_min_sale_qty`, `max_sale_qty`, `use_config_max_sale_qty`, `is_in_stock`, `low_stock_date`, `notify_stock_qty`, `use_config_notify_stock_qty`, `manage_stock`, `use_config_manage_stock`, `stock_status_changed_auto`, `use_config_qty_increments`, `qty_increments`, `use_config_enable_qty_inc`, `enable_qty_increments`, `is_decimal_divided`, `website_id` FROM `cataloginventory_stock_item` AS `CataloginventoryStockItem` WHERE `CataloginventoryStockItem`.`product_id` IN (57, 58, 77, 89);

Tier Price

SELECT `value_id`, `entity_id`, `all_groups`, `customer_group_id`, `qty`, `value`, `website_id`, `percentage_value` FROM `catalog_product_entity_tier_price` AS `CatalogProductEntityTierPrice` WHERE `CatalogProductEntityTierPrice`.`entity_id` IN (57, 58, 77, 89);

Now you know how to replace Adobe Commerce's broken feature using RAW SQL and any language you want.

Using Magento micro service code to send proper SQL queries to magento will looks like :

NodeJS:

let Product = await CatalogProductEntity.findAll({
where: {'sku': ['24-MB01', '24-MB04', '24-WG084', '24-WG085']},        include: [{
model: models.CatalogProductEntityVarchar, as: 'CatalogProductEntityVarchars', where: {attribute_id: VisibleOnFront,store_id: [0, 1]},required: false,                attributes: ['store_id', 'value', 'attribute_id'],                separate: true},
{model: models.CatalogProductEntityInt, as: 'CatalogProductEntityInts', where: {attribute_id: VisibleOnFront,                    store_id: [0, 1]},required: false,attributes: ['store_id', 'value', 'attribute_id'],separate: true},
{model: models.CatalogProductEntityText, as: 'CatalogProductEntityTexts',where: {attribute_id: VisibleOnFront,                    store_id: [0, 1]}, required: false, attributes: ['store_id', 'value', 'attribute_id'], separate: true},
{model: models.CatalogProductEntityDecimal, as: 'CatalogProductEntityDecimals', where: { attribute_id: VisibleOnFront, store_id: [0, 1]}, required: false,                attributes: ['store_id', 'value', 'attribute_id'],                separate: true}});

GitHub - Genaker/nodejento: NodeJS implementation of the Magento 2 ORM data access layer without…

Python:

products = db.query(Product).options(selectinload("varchar"), selectinload(Product.intager), selectinload(Product.text), selectinload(Product.decimal), selectinload(Product.datetime), selectinload(Product.gallery)).filter(Product.sku != "Test001").limit(100).all()

GitHub - Genaker/PyGento: Python module to work with Magneto Database directly without using broken…

Laravel:

$products = Laragento\Models\CatalogProductEntity::where('sku', '21157')->with('catalog_product_entity_varchars','catalog_product_entity_ints','catalog_product_entity_texts','catalog_product_entity_decimals')->get();

GitHub - Genaker/laragento: Collection of the Laravel/Eloquent Model classes that allows you to get…

URL rewrite Magento 2 Query :

SELECT `url_rewrite`.*, `relation`.`category_id`, `relation`.`product_id` FROM `url_rewrite` LEFT JOIN `catalog_url_rewrite_product_category` AS `relation` ON url_rewrite.url_rewrite_id = relation.url_rewrite_id WHERE (url_rewrite.entity_type IN (?)) AND (url_rewrite.entity_id IN (?)) AND (url_rewrite.store_id IN (?)) AND (url_rewrite.is_autogenerated IN (?)) AND (relation.category_id IS NULL)

Qurey sent by Magento community :

SELECT `catalog_product_entity_int`.`entity_id`, `catalog_product_entity_int`.`attribute_id`, `catalog_product_entity_int`.`value` FROM `catalog_product_entity_int` WHERE (entity_type_id =4) AND (entity_id IN (?)) AND (attribute_id IN (?)) AND (store_id = 0) UNION ALL SELECT `catalog_product_entity_varchar`.`entity_id`, `catalog_product_entity_varchar`.`attribute_id`, `catalog_product_entity_varchar`.`value` FROM `catalog_product_entity_varchar` WHERE (entity_type_id =4) AND (entity_id IN (?)) AND (attribute_id IN (?)) AND (store_id = 0) UNION ALL SELECT `catalog_product_entity_decimal`.`entity_id`, `catalog_product_entity_decimal`.`attribute_id`, `catalog_product_entity_decimal`.`value` FROM `catalog_product_entity_decimal` WHERE (entity_type_id =4) AND (entity_id IN (?)) AND (store_id = 0) UNION ALL SELECT `catalog_product_entity_text`.`entity_id`, `catalog_product_entity_text`.`attribute_id`, `catalog_product_entity_text`.`value` FROM `catalog_product_entity_text` WHERE (entity_type_id =4) AND (entity_id IN (?)) AND (attribute_id IN (?)) AND (store_id = 0) UNION ALL SELECT `catalog_product_entity_datetime`.`entity_id`, `catalog_product_entity_datetime`.`attribute_id`, `catalog_product_entity_datetime`.`value` FROM `catalog_product_entity_datetime` WHERE (entity_type_id =4) AND (entity_id IN (?)) AND (attribute_id IN (?)) AND (store_id = 0);