how to get product specific attribute codes & values using raw sql query?

For Magento version 2.2, where a new redirection has been added in their EAV model, below query is working for my project. There the entity_type_id needed to be retrieved from catalog_product_entity.

SELECT    ce.sku,           
        ea.attribute_code,
        CASE ea.backend_type
           WHEN 'varchar' THEN ce_varchar.value
           WHEN 'int' THEN ce_int.value
           WHEN 'text' THEN ce_text.value
           WHEN 'decimal' THEN ce_decimal.value
           WHEN 'datetime' THEN ce_datetime.value
           ELSE ea.backend_type
        END AS value
    FROM (select cpe.sku, eas.entity_type_id, cpe.entity_id 
                FROM catalog_product_entity AS cpe, eav_attribute_set AS eas
                WHERE cpe.attribute_set_id=eas.attribute_set_id) AS ce
    LEFT JOIN eav_attribute AS ea
        ON ce.entity_type_id = ea.entity_type_id
    LEFT JOIN catalog_product_entity_varchar AS ce_varchar
        ON ce.entity_id = ce_varchar.entity_id
        AND ea.attribute_id = ce_varchar.attribute_id
        AND ea.backend_type = 'varchar'
    LEFT JOIN catalog_product_entity_int AS ce_int
        ON ce.entity_id = ce_int.entity_id
        AND ea.attribute_id = ce_int.attribute_id
        AND ea.backend_type = 'int'
    LEFT JOIN catalog_product_entity_text AS ce_text
        ON ce.entity_id = ce_text.entity_id
        AND ea.attribute_id = ce_text.attribute_id
        AND ea.backend_type = 'text'
    LEFT JOIN catalog_product_entity_decimal AS ce_decimal
        ON ce.entity_id = ce_decimal.entity_id
        AND ea.attribute_id = ce_decimal.attribute_id
        AND ea.backend_type = 'decimal'
    LEFT JOIN catalog_product_entity_datetime AS ce_datetime
        ON ce.entity_id = ce_datetime.entity_id
        AND ea.attribute_id = ce_datetime.attribute_id
        AND ea.backend_type = 'datetime';

You are doing more harm than good by avoiding the proper way of handling this. I would like to be sure that people know this is very much unacceptable if developing for a client or on a live production site.

The proper method would be to obtain the product collection, where you can apply any required filtering methods to reduce the collection to whatever is needed. Once you have your collection, then use the Magento Framework walk() method defined in the core/resource_iterator model class: ./app/code/core/Mage/Core/Model/Resource/Iterator.php.

An example is fairly well illustrated within magento/app/code/core/Mage/Rss/Block/Catalog/NotifyStock.php:

 // ...
        $product = Mage::getModel('catalog/product');
        /* @var $collection Mage_Catalog_Model_Resource_Product_Collection */
        $collection = $product->getCollection();
        Mage::getResourceModel('cataloginventory/stock')->addLowStockFilter($collection, array(
            'qty',
            'notify_stock_qty',
            'low_stock_date',
            'use_config' => 'use_config_notify_stock_qty'
        ));
        $collection
            ->addAttributeToSelect('name', true)
            ->addAttributeToFilter('status',
                array('in' => Mage::getSingleton('catalog/product_status')->getVisibleStatusIds())
            )
            ->setOrder('low_stock_date');
         
        /*
        using resource iterator to load the data one by one instead of loading all at the same time. loading all data at the same time can cause big memory allocation.
        */
        Mage::getSingleton('core/resource_iterator')->walk(
            $collection->getSelect(),
            array(array($this, 'addNotifyItemXmlCallback')),
            array('rssObj'=> $rssObj, 'product'=>$product, 'globalQty' => $globalNotifyStockQty)
        );

I also think you may be unaware that EAV attribute table relations within Magento are not handled with left join, instead, they are actually done with union

Following code is working fine.

 <?php
    include ('app/Mage.php');
    Mage::app();
    ini_set('display_errors',1);
    $resource = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');
    $query = "SELECT    ce.sku,           
            ea.attribute_code,
            CASE ea.backend_type
               WHEN 'varchar' THEN ce_varchar.value
               WHEN 'int' THEN ce_int.value
               WHEN 'text' THEN ce_text.value
               WHEN 'decimal' THEN ce_decimal.value
               WHEN 'datetime' THEN ce_datetime.value
               ELSE ea.backend_type
            END AS value
        FROM (select sku ,entity_type_id,entity_id from catalog_product_entity limit 100) AS ce
        LEFT JOIN eav_attribute AS ea
            ON ce.entity_type_id = ea.entity_type_id
        LEFT JOIN catalog_product_entity_varchar AS ce_varchar
            ON ce.entity_id = ce_varchar.entity_id
            AND ea.attribute_id = ce_varchar.attribute_id
            AND ea.backend_type = 'varchar'
        LEFT JOIN catalog_product_entity_int AS ce_int
            ON ce.entity_id = ce_int.entity_id
            AND ea.attribute_id = ce_int.attribute_id
            AND ea.backend_type = 'int'
        LEFT JOIN catalog_product_entity_text AS ce_text
            ON ce.entity_id = ce_text.entity_id
            AND ea.attribute_id = ce_text.attribute_id
            AND ea.backend_type = 'text'
        LEFT JOIN catalog_product_entity_decimal AS ce_decimal
            ON ce.entity_id = ce_decimal.entity_id
            AND ea.attribute_id = ce_decimal.attribute_id
            AND ea.backend_type = 'decimal'
        LEFT JOIN catalog_product_entity_datetime AS ce_datetime
            ON ce.entity_id = ce_datetime.entity_id
            AND ea.attribute_id = ce_datetime.attribute_id
            AND ea.backend_type = 'datetime'";
    $results = $readConnection->fetchAll($query);