MySQL JOINs explained with WordPress database examples

Home » Coding » MySQL JOINs explained with WordPress database examples
October 5, 2021 Coding, MySQL
MySQL JOINs explained with WordPress database examples

SQL JOINs are the mechanism for combining rows from two or more tables based on a related column, and they are fundamental to any meaningful WordPress database query that crosses table boundaries. The WordPress database schema is heavily normalised: post data lives in wp_posts, meta data in wp_postmeta, taxonomy assignments in wp_term_relationships, and term names in wp_terms. Fetching a post along with all its categories, tags, and meta values therefore requires multiple joins. The four most common join types are: INNER JOIN (returns only rows that have a match in both tables), LEFT JOIN (returns all rows from the left table and matching rows from the right, with NULL where no match exists), RIGHT JOIN (the mirror of LEFT JOIN), and CROSS JOIN (Cartesian product — rarely useful). In WordPress queries you almost always want LEFT JOIN rather than INNER JOIN, because inner join silently excludes posts that have no meta or no taxonomy terms. Understanding join behaviour is especially important when you write custom $wpdb queries and when analysing them with EXPLAIN to check that indexes are being used on the joined columns. Every WordPress join should be on indexed columns — post_id, object_id, term_taxonomy_id — or your queries will become very slow at scale.

Problem: You need to query data from multiple WordPress tables in a single SQL statement and are not sure which JOIN type to use or how to structure the query.

Solution: Use the following query patterns as a reference in phpMyAdmin or $wpdb:

-- INNER JOIN: posts that have at least one postmeta row
SELECT p.ID, p.post_title, pm.meta_value AS featured_img
FROM ynYYAa7bFT_posts p
INNER JOIN ynYYAa7bFT_postmeta pm
    ON pm.post_id = p.ID AND pm.meta_key = '_thumbnail_id'
WHERE p.post_status = 'publish' AND p.post_type = 'post';

-- LEFT JOIN: all published posts, NULL if no featured image
SELECT p.ID, p.post_title, pm.meta_value AS featured_img
FROM ynYYAa7bFT_posts p
LEFT JOIN ynYYAa7bFT_postmeta pm
    ON pm.post_id = p.ID AND pm.meta_key = '_thumbnail_id'
WHERE p.post_status = 'publish' AND p.post_type = 'post';

-- Multiple JOINs: post with its category names
SELECT p.ID, p.post_title,
       GROUP_CONCAT(t.name SEPARATOR ', ') AS categories
FROM ynYYAa7bFT_posts p
LEFT JOIN ynYYAa7bFT_term_relationships tr ON tr.object_id = p.ID
LEFT JOIN ynYYAa7bFT_term_taxonomy tt
    ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'category'
LEFT JOIN ynYYAa7bFT_terms t ON t.term_id = tt.term_id
WHERE p.post_status = 'publish' AND p.post_type = 'post'
GROUP BY p.ID
ORDER BY p.post_date DESC
LIMIT 10;

-- Self-join: find posts and their parent page titles
SELECT child.ID, child.post_title AS child_title,
       parent.post_title AS parent_title
FROM ynYYAa7bFT_posts child
LEFT JOIN ynYYAa7bFT_posts parent ON parent.ID = child.post_parent
WHERE child.post_type = 'page' AND child.post_status = 'publish'
ORDER BY child.menu_order;

NOTE: When joining wp_term_taxonomy, always include the taxonomy = ‘category’ (or whatever taxonomy you need) condition on the join itself (ON ... AND tt.taxonomy = ‘category’) rather than in the WHERE clause. Putting it in WHERE converts your LEFT JOIN into an effective INNER JOIN by filtering out the NULL rows, which silently excludes posts with no terms in that taxonomy.

Related posts