mysql - 查询元数据键不存在的行

Question

SELECT DISTINCT wposts.ID AS ID
FROM `wp_posts` AS wposts
JOIN `wp_postmeta` AS postmeta ON (wposts.ID = postmeta.post_id)
WHERE wposts.post_type = 'post'
AND wposts.ID NOT IN (
    SELECT wp_posts.ID FROM wp_posts, `wp_postmeta` AS postmeta2
    WHERE wp_posts.ID = postmeta2.post_id
    AND postmeta2.meta_key = 'z_latitude'
)
ORDER BY wposts.post_date DESC

查询一团糟。我只是想查询没有特定元键的所有行。在上面，我查询了具有元键的所有行，然后将它们从外部查询中排除。我不知道如何写得更好。

Answer 1

通过左联接使用外联接并抓取未联接的行:

SELECT DISTINCT wposts.ID AS ID
FROM `doxy_posts` AS wposts
left JOIN `doxy_postmeta` AS postmeta ON wposts.ID = postmeta.post_id
WHERE postmeta.post_id is null
ORDER BY wposts.post_date DESC