MySQL subqueries explained with WordPress database examples

Home » Coding » MySQL subqueries explained with WordPress database examples
November 5, 2021 Coding, MySQL
MySQL subqueries explained with WordPress database examples

A subquery is a SELECT statement nested inside another SQL statement, used as a value, a condition, or a derived table. Subqueries let you write queries that would otherwise require two separate round-trips to the database — for example, finding all posts whose author has published more than ten posts, or deleting all orphaned post-meta rows for posts that no longer exist. MySQL supports subqueries in three positions: in the WHERE clause (correlated or non-correlated), in the FROM clause (as a derived table), and in the SELECT list (as a scalar subquery). Non-correlated subqueries run once and their result is reused; correlated subqueries run once per row of the outer query and can be significantly slower. The EXISTS operator is often more efficient than IN (SELECT ...) for large datasets because it short-circuits as soon as one match is found. In WordPress, subqueries are most useful for orphan cleanup, conditional deletions, and complex filtering that WP_Query cannot express. Always run EXPLAIN on subqueries to verify they are not causing full-table scans, and consider rewriting correlated subqueries as JOINs (as covered in the JOINs guide) when performance is critical. The examples below use WordPress tables directly so you can test them in phpMyAdmin immediately.

Problem: You need to filter, delete, or aggregate WordPress database records based on criteria from another table, without making two separate database calls.

Solution: Use the following subquery patterns in phpMyAdmin or via $wpdb:

-- IN subquery: posts by authors who have published more than 5 posts
SELECT ID, post_title, post_author
FROM ynYYAa7bFT_posts
WHERE post_status = 'publish'
  AND post_type   = 'post'
  AND post_author IN (
      SELECT post_author
      FROM ynYYAa7bFT_posts
      WHERE post_status = 'publish' AND post_type = 'post'
      GROUP BY post_author
      HAVING COUNT(*) > 5
  );

-- NOT EXISTS: find orphaned postmeta (meta for deleted posts)
SELECT pm.meta_id, pm.post_id, pm.meta_key
FROM ynYYAa7bFT_postmeta pm
WHERE NOT EXISTS (
    SELECT 1 FROM ynYYAa7bFT_posts p WHERE p.ID = pm.post_id
);

-- Clean up orphaned postmeta (use START TRANSACTION first in production!)
DELETE pm FROM ynYYAa7bFT_postmeta pm
WHERE NOT EXISTS (
    SELECT 1 FROM ynYYAa7bFT_posts p WHERE p.ID = pm.post_id
);

-- Derived table (subquery in FROM): average posts per category
SELECT AVG(cat_post_count) AS avg_posts_per_category
FROM (
    SELECT COUNT(tr.object_id) AS cat_post_count
    FROM ynYYAa7bFT_term_taxonomy tt
    JOIN ynYYAa7bFT_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
    WHERE tt.taxonomy = 'category'
    GROUP BY tt.term_id
) AS category_counts;

-- Scalar subquery in SELECT: post title + total post count for that author
SELECT post_title,
       (SELECT COUNT(*) FROM ynYYAa7bFT_posts p2
        WHERE p2.post_author = p.post_author AND p2.post_status = 'publish') AS author_total
FROM ynYYAa7bFT_posts p
WHERE post_status = 'publish' AND post_type = 'post'
ORDER BY author_total DESC LIMIT 5;

NOTE: The DELETE FROM using NOT EXISTS is a common database maintenance query — always wrap it in a START TRANSACTION and verify the row count with a matching SELECT first. On large tables, consider adding a LIMIT 1000 and running the delete in batches to avoid locking the table for extended periods. The WordPress WP-CLI command wp db optimize reclaims the freed space after bulk deletes.

Related posts