Delete Users Without Posts from WordPress

Ever let your WordPress users database get a bit out of control? If you do not allow user registration but have too many users that have no posts or reason to be on your site here is a quick SQL query to clear those users out of your database. Yes you could probably just delete them from the dashboard, and yes this is a pretty rare use case… but I needed this just now so I’m sharing it!

Make a backup of your database before you read any further. I am not responsible if you delete your users and cannot bring them back. You’ve been warned.

First delete the users, where the wp_users.ID is not found in the wp_posts.post_author column. We use distinct here because a single user ID could be the author of 1000 posts, and be returned 1000 times when we only need it once.

delete from wp_users where wp_users.ID not in (select distinct(post_author) from wp_posts);

Think your done? Look in your wp_usermeta table!

Now we delete all the wp_usermeta entries where the wp_usermeta.user_id is not found in the wp_users.ID column. Similair to the last query but just pointing at different tables and removing the distinct since the ID column is already all unique values.

delete from wp_usermeta where wp_usermeta.user_id not in (select ID from wp_users);