Why Does This SQL INNER JOIN Work and This Doesn't?

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?

please provide error that you receive when you submit this query. — ilan berci, Mar 15 '13 at 18:21
No error is generated. The problem is just that the proper results are not returned. The first query returns 4 rows from the database that match the criteria. The second returns 0. — Dominic P, Mar 15 '13 at 18:24

woemler · Answer 1 · 2013-03-15T18:47:01.590

1

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

edited Mar 15 '13 at 18:47

answered Mar 15 '13 at 18:23

woemler

7,089
7
48
67

Thanks for the explanation. The problem with the first query is that when you have a more complex version (as shown in the question I referenced) the multiple INNER JOINs become a massive performance hit. So, I was trying to find a way to get the results without so many INNER JOINs. – Dominic P Mar 15 '13 at 18:41
I understand. I updated my answer to add another option you can use. – woemler Mar 15 '13 at 18:47
Wow, that's really cool. I didn't think you could do that in the INNER JOIN statement. I'm having trouble understanding the why this works though. I see you're combining the multiple clauses in the join (formerly WHERE clauses) with OR. I would think that would fail because I want all of the conditions to be true. But, it works as expected. Can you help me understand why? – Dominic P Mar 15 '13 at 19:29
I can't say for certain if this is true in MySQL (I mostly use Oracle), but I believe that having the statements that define links between tables in the `WHERE` or `ON` clauses results in the same query execution plan. You want certain combinations of values to be true, but not all of them all the time, this is why you need the `OR`. If you were only comparing `wp_postmeta.meta_key` values, you could say `...AND wp_postmeta.meta_key in ('_visibility', '_stock_status')...`, and there would be no need for an `OR`. – woemler Mar 15 '13 at 19:34
Ok, that makes sense. I'm beginning to understand this more. Since I need _all_ of the conditions to be true, I'm pretty much stuck with multiple INNER JOINs. – Dominic P Mar 15 '13 at 19:45
This solution does work in solving the SQL statement issue, BUT in the context of the linked question that I responded to, it's not directly applicable on WordPress since it's generated automatically and you'd have to manually override it through filters to convert an adhoc (not always having the exact same components) query to this format. – KalenGi Mar 16 '13 at 06:52

Richard Sitze · Accepted Answer · 2013-03-15T19:06:21.627

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' )
)

edited Mar 15 '13 at 19:06

answered Mar 15 '13 at 18:28

Richard Sitze

8,262
3
36
48

Thanks for the explanation. That's kind of what I thought, but I wasn't sure. Do you know of any way to rewrite the 1st query that would result in fewer INNER JOINs, or is this just not possible? – Dominic P Mar 15 '13 at 18:39
Your question suggests that you didn't see my update regarding changing some ANDs to ORs. – Richard Sitze Mar 15 '13 at 19:07
Thanks, I did see the update, but I feel like that query will not retrieve the same information as the first one. I need `'_visibility'` to be 'visible/catalog' __AND__ `'_stock_status'` to be 'instock'. Wouldn't the modified version return results that have either condition but not necessarily both? – Dominic P Mar 15 '13 at 19:34
@RichardSitze I agree with your explanation. Each row of wp_postmeta is tested against ALL conditions and shall always fail if AND is used instead of OR. The solution I provided on the linked question worked for my case since what I required was OR. – KalenGi Mar 16 '13 at 07:05

Why Does This SQL INNER JOIN Work and This Doesn't?

2 Answers2

Linked