Outer Joins—Finding items in one population, but not another

Description

Outer joins are most commonly used to find data that is in one population or table, but not another. Below is a prime example of this using the Wordpress Tables as a sample.

Problem:

Find posts in the posts table that do not have an associated post meta of primary_image

Solution:

SELECT * 

FROM `wp_posts` as p left join (select * 

from `wp_postmeta` 

where meta_key = 'primary_image') as pm on p.ID = pm.post_id 

WHERE p.post_type = 'news' and pm.post_id is null

Explanation:

In this case, the solution is to do a subquery from the postmeta table to find all items that have a primary image, THEN do an outer join from the posts table to this subset of data from the postmeta and find all items that have a null value on the primary key that you are joining.. this will give you all the items in the left side of the join that the system could not find a value to join to in the right side of the join

Example:

Table A Table B (result of subquery)

PK Value PK Meta_key

1 ABC 1 primary_image

2 ABC  3 primary_image

3 ABC

Intermediary result after outer left join (prior to where clause)

1 ABC 1 primary_image

2 ABC null null

3 ABC 3 prmiary_image

Final Result after application of where clause

2 ABC null null

Conclusion

The query finds all the entries in posts table that don't have a value in the subquery result, which is the subset of items that have a primary_image key in the post meta table. Thus, it finds posts in the posts table that do not have an associated post meta of primary_image