Why Does This SQL INNER JOIN Work and This Doesn&#

Question

I'm still pretty new to SQL, and I'm having trouble wrapping my mind around why one of these queries functions properly and one does not. This is stemming from my attempts to optimize a complex and slow query. @kalengi suggested what looks like a brilliant solution to me, but it doesn't seem to work on my site. Here are the queries.

This is the standard SQL that WordPress generates (this is working as expected):

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE 1=1 
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish')
AND (
   (wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog'))
   AND  (mt1.meta_key = '_stock_status' AND CAST(mt1.meta_value AS CHAR) = 'instock')
)
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order,wp_posts.post_title asc
LIMIT 0, 10

This is the SQL after @kalengi's filter processes it to combine the multiple INNER JOINs into one (this returns 0 results):

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
INNER JOIN wp_postmeta AS pmta ON (wp_posts.ID = pmta.post_id) 
WHERE 1=1 
AND wp_posts.post_type = 'product'
AND ( wp_posts.post_status = 'publish' )
AND (
  ( pmta.meta_key = '_visibility' AND CAST(pmta.meta_value AS CHAR) IN ( 'visible','catalog' ) )
  AND ( pmta.meta_key = '_stock_status' AND CAST(pmta.meta_value AS CHAR) = 'instock' )
)
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order,wp_posts.post_title asc
LIMIT 0, 10

Can anyone explain whey the second one doesn't work to me?

Answer 1

The two inner joins:

INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)

represent two data sets (that happen to be "the same").

Given the WHERE condition, the results will be sets of two rows where the first (wp_postmeta) meets one condition:

   (wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog'))

And the second meets a completely different condition:

   (mt1.meta_key = '_stock_status' AND CAST(mt1.meta_value AS CHAR) = 'instock')

By combining everything into one INNER JOIN, you're instead looking for ONE ROW which matches both conditions. Apparently there isn't one.

If you review the "brilliant solution", you'll see that 'AND's were changed to 'OR' to preserve the semantics:

AND (
  ( pmta.meta_key = '_visibility' AND CAST(pmta.meta_value AS CHAR) IN ( 'visible','catalog' ) )
  OR ( pmta.meta_key = '_stock_status' AND CAST(pmta.meta_value AS CHAR) = 'instock' )
)

Answer 2

It looks like you have a condition in your WHERE clause that is looking for two values at the same time in the pmta.meta_key and pmta.meta_value columns:

....
( pmta.meta_key = '_visibility' AND CAST(pmta.meta_value AS CHAR) IN ('visible','catalog' ) )
AND ( pmta.meta_key = '_stock_status' AND CAST(pmta.meta_value AS CHAR) = 'instock' )
....

One column cannot have two different values in the same row, so this test will return FALSE, and consequently, no rows will be returned.

If you rewrite the original query to group the join conditions into the ON clauses, you can see why your second query won't work:

SELECT 
    SQL_CALC_FOUND_ROWS wp_posts.ID
FROM 
    wp_posts 
    INNER JOIN wp_postmeta 
        ON wp_posts.ID = wp_postmeta.post_id
        AND wp_postmeta.meta_key = '_visibility' 
        AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog')
    INNER JOIN wp_postmeta AS mt1 
        ON wp_posts.ID = mt1.post_id
        AND mt1.meta_key = '_stock_status' 
        AND CAST(mt1.meta_value AS CHAR) = 'instock'
WHERE 
    1=1 
    AND wp_posts.post_type = 'product'
    AND wp_posts.post_status = 'publish'
GROUP BY 
    wp_posts.ID
ORDER BY 
    wp_posts.menu_order,
    wp_posts.post_title asc
LIMIT 0, 10

If you want to join the table only once, try something like this:

SELECT 
    SQL_CALC_FOUND_ROWS wp_posts.ID
FROM 
    wp_posts 
    INNER JOIN wp_postmeta 
        ON wp_posts.ID = wp_postmeta.post_id
        AND (
            wp_postmeta.meta_key = '_visibility' 
            AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog')
        ) OR (
            wp_postmeta.meta_key = '_stock_status' 
            AND CAST(wp_postmeta.meta_value AS CHAR) = 'instock'
        )
WHERE 
    1=1 
    AND wp_posts.post_type = 'product'
    AND wp_posts.post_status = 'publish'
GROUP BY 
    wp_posts.ID
ORDER BY 
    wp_posts.menu_order,
    wp_posts.post_title asc
LIMIT 0, 10