What’s Going On?

Have you ever needed to select a subset of a table, based on non-contiguous identifiers? I used to either use a join for that or write an external script to SELECT * FROM whatever WHERE key='val', then loop through the results using them as needed. Recently, I ran across this neat little SQL operator, the IN operator; it does exactly what it sounds like – it operates on values that are IN a list of values.

As if that’s not good enough, I found out that it also accepts subqueries that return identifiers, so say goodby to the need for a script that loops through a bunch of IDs; simply put the query that returns the list of ids in the IN clause:

SELECT *
FROM series 
WHERE id IN ( 
   SELECT series_id from youtube_videos 
)

Can you guess what that does? Yeah, that’s right — it selects all information about series that have a youtube video. Or course, you’ll need a database with those two tables in them first, but hypothetically speaking, this gets the job done a whole lot faster than some other equivalents.

Why Did I Run Across This?

I had a WordPress install where a bunch of normal posts needed moved to a Custom Post Type, but here’s the catch: only the posts of a certain category should be moved. Since the category is stored in a different table (wp_term_relationships), it wasn’t just as simple as running:

UPDATE wp_posts SET post_type = "custom_type"
WHERE post_category LIKE "category-name";

If I added a JOIN, I might possibly have been able to do it:

UPDATE wp_posts p SET p.post_type = "custom_type"
WHERE tr.term_taxonomy_id = :category
INNER JOIN wp_terms_relationships tr ON tr.object_id = p.ID;

That’s kind of ugly; can we clean it up any? Yes, we can.

UPDATE wp_posts SET post_type = "custom_type"
WHERE ID IN (
    SELECT object_id FROM wp_term_relationships
    WHERE term_taxonomy_id = :category;
);

Isn’t that kind of cool?