Custom SQL query to get List of posts with featured image url

The accepted answer will ruin your life LOL... Here's the easy way..

global $wpdb;

$uploadDir = wp_upload_dir();
$uploadDir = $uploadDir['baseurl'];

$page = (!empty($_REQUEST['p'])) ? $_REQUEST['p'] : 1;
$_limit = 50;
$_start = ($page > 1) ? ($page * $_limit) : 0;

$_stmts = "
    SELECT 
        p.ID, 
        p.post_title,
        p.post_name,
        CONCAT( '".$uploadDir."', '/', thumb.meta_value) as thumbnail
    FROM {$wpdb->prefix}posts AS p
        
        LEFT JOIN {$wpdb->prefix}postmeta AS thumbnail_id
            ON thumbnail_id.post_id = p.ID AND thumbnail_id.meta_key = '_thumbnail_id'
        
        LEFT JOIN {$wpdb->prefix}postmeta AS thumb
            ON thumb.post_id = thumbnail_id.meta_value AND thumb.meta_key = '_wp_attached_file'
        
        
    WHERE p.post_status = 'publish'
    LIMIT {$_start},{$_limit}
";

$get_rs = $wpdb->get_results($_stmts);

This is much simpler solution without any use of complex joins,

SELECT wp_posts.id,
       wp_posts.post_title,
       wp_terms.name,
       (SELECT guid
        FROM   wp_posts
        WHERE  id = wp_postmeta.meta_value) AS image
FROM   wp_posts,
       wp_postmeta,
       wp_term_relationships,
       wp_terms
WHERE  wp_posts.id = wp_term_relationships.object_id
       AND wp_terms.term_id = wp_term_relationships.term_taxonomy_id
       AND wp_terms.name = 'mycat'
       AND wp_posts.post_status = "publish"
       AND wp_posts.post_type = "post"
       AND wp_postmeta.post_id = wp_posts.id
       AND wp_postmeta.meta_key = '_thumbnail_id'
ORDER  BY wp_posts.post_date DESC
LIMIT  5;

This query will give post id, post category, and featured image. You can filter the category by changing wp_terms.name = 'mycat' with your category name.

Worked on similar problem recently. Here is the SQL query to get the post with Featured Image.

global $wpdb;

$perpage = 10; 
$page = 1; // Get the current page FROM $wp_query

$counter = $perpage * $page;

$uploadDir = wp_upload_dir();
$uploadDir = $uploadDir['baseurl'];

$sql = "
SELECT 
    post.ID,
    post.post_title,
    post.post_date,
    post.category_name,
    post.category_slug,
    post.category_id,
    CONCAT( '".$uploadDir."','/', thumb.meta_value) as thumbnail,
    post.post_type
FROM (
    SELECT  p.ID,   
          p.post_title, 
          p.post_date,
          p.post_type,
          MAX(CASE WHEN pm.meta_key = '_thumbnail_id' then pm.meta_value ELSE NULL END) as thumbnail_id,
      term.name as category_name,
      term.slug as category_slug,
      term.term_id as category_id
    FROM ".$wpdb->prefix."posts as p 
    LEFT JOIN ".$wpdb->prefix."postmeta as pm ON ( pm.post_id = p.ID)
    LEFT JOIN ".$wpdb->prefix."term_relationships as tr ON tr.object_id = p.ID
    LEFT JOIN ".$wpdb->prefix."terms as term ON tr.term_taxonomy_id = term.term_id
    WHERE 1 ".$where." AND p.post_status = 'publish'
    GROUP BY p.ID ORDER BY p.post_date DESC
  ) as post
  LEFT JOIN ".$wpdb->prefix."postmeta AS thumb 
    ON thumb.meta_key = '_wp_attached_file' 
    AND thumb.post_id = post.thumbnail_id
  LIMIT ".$counter.",".$perpage;

$posts = $wpdb->get_results( $sql, ARRAY_A); 

Bonus : You will also get Category details with post details if you need.

P.S : You will need to change the query a bit to match your requirements and get desired fields.

You can manage the page load speed by using infinite scroll method. Retrieve only those post which are showing above the fold and on scroll you can query other posts this can help you load your page much more quickly. Here is a tutorial for this.

https://code.tutsplus.com/tutorials/how-to-create-infinite-scroll-pagination--wp-24873

There are some plugins for infinite scroll for posts.

https://wordpress.org/plugins/wp-infinite-scrolling/

The post and featured image URL is saved in wp_posts table and its relation is saved in wp_postmeta table so in any case you have to query both these tables either directly in single Query or using WordPress function and query them separately.

I don't think querying both tables in single Query will improve major performance but if you want to do it then you can use below custom code.

    global $wpdb;
    $results = $wpdb->get_results( "SELECT * FROM $wpdb->posts, $wpdb->postmeta where $wpdb->posts.ID = $wpdb->postmeta.post_id and $wpdb->postmeta.meta_key = '_thumbnail_id' and $wpdb->posts.post_type='post' limit 100");

    if ( $results )
    {
        foreach ( $results as $post )
        {       
            setup_postdata( $post );
            ?>
            <h2>
                <a href="<?php the_permalink(); ?>" rel="bookmark" title="Permalink: <?php the_title(); ?>">
                    <?php the_title(); ?>
                </a>
            </h2>
            <?php
            if ( $post->meta_value ) { 
                $image = image_downsize( $post->meta_value );
                ?>
                <img src="<?php echo $image[0]; ?>" />
                <?php
            }
        }   
    }
    else
    {
        ?>
        <h2>Not Found</h2>
        <?php
    }