I need to get all posts that do not have a particular custom field, (e.g. custom_field).

The following query works fine:

SELECT * FROM wp_posts WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = "custom_field")

I try to avoid using subqueries if possible. How can this query be rewritten so it doesn't use a subquery? I tried writing it with a JOIN, but I don't know how to avoid joining the rows for post X entirely if a particular meta_key custom field is present.

For example, a naive attempt might be:

SELECT p.* FROM wp_posts p JOIN wp_postmeta pm ON p.id = pm.post_id AND pm.meta_key != "custom_field"

However, this query simply avoids joining on rows with a meta_key matching "custom_field". Other rows for that post will still be joined. We wish to get all posts that don't have a custom field with a meta_key of "custom_field".

Any thoughts?


This is the way WordPress does it:

SELECT wp_posts.* FROM wp_posts
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'custom_field' )
WHERE wp_postmeta.post_id IS NULL

You can do this with WP_Query meta_query and 'compare' => 'NOT EXISTS'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.