为什么此SQL INNER JOIN有效，却无效? [英] Why Does This SQL INNER JOIN Work and This Doesn't?

问题描述

我对SQL还是很陌生，我无法确定为什么其中一个查询可以正常运行而另一个查询却不能正常运行.这源于我尝试优化复杂而缓慢的查询. @kalengi 建议看起来像出色的解决方案，但在我的网站上似乎无效.这是查询.

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.

这是WordPress生成的标准SQL(按预期运行):

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

这是@kalengi的过滤器对其进行处理以将多个INNER JOIN合并为一个(返回0个结果)之后的SQL:

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?

推荐答案

两个内部联接:

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").

鉴于WHERE条件，结果将是两行的集合，其中第一行(wp_postmeta)满足一个条件:

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

通过将所有内容组合为一个INNER JOIN，您将寻找一个同时符合这两个条件的行.显然没有.

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

如果您查看出色的解决方案"，您会发现"AND"已更改为"OR"以保留语义:

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

这篇关于为什么此SQL INNER JOIN有效，却无效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！