WHERE IN

Our blogging website, Tedium, is having problems with bad actors! Users are reporting posts spreading misinformation. We've gotten multiple reports about the users with email Ben.Cummerata94@hotmail.com and Audrey50@yahoo.com.

We better look at all of their posts to investigate. We already know 2 ways to do this.

The first is to get the blogger IDs from bloggers, then use those to get the blog_posts in a separate query.

SELECT id
FROM bloggers
WHERE email IN ('Ben.Cummerata94@hotmail.com', 'Audrey50@yahoo.com');

We can copy those IDs and match against them with the IN operator.

SELECT *
FROM blog_posts
WHERE blogger_id IN (1, 10);

When we learned JOIN, we found out how to do this in a single query.

SELECT p.*
FROM bloggers b
JOIN blog_posts p
  ON p.blogger_id = b.id
WHERE b.email IN ('Ben.Cummerata94@hotmail.com', 'Audrey50@yahoo.com');

This works just fine, but queries with JOINs can get a little complicated and difficult to understand. Many people find the first way more straightforward, even if it is 2 separate queries. Is there a way to do that in 1 single query that resembles the first approach?

Imagine something like…

SELECT *
FROM blog_posts
WHERE blogger_id IN (bad_blogger_ids);

…where we can substitute (bad_blogger_ids) with a subquery for those IDs.

Subquery to Expression List

Here's the solution using a subquery.

SELECT *
FROM blog_posts
WHERE blogger_id IN (
  SELECT id
  FROM bloggers
  WHERE email IN ('Ben.Cummerata94@hotmail.com', 'Audrey50@yahoo.com')
);

We previously learned that the WHERE clause expects a value expression, and subqueries can only be in value expressions if they return 1 row and 1 column. However, in this example, the subquery returns 2 rows. Why is this allowed? The difference here is that IN takes an expression list and returns either TRUE or FALSE. Subqueries can be converted to an expression list if they return 1 column, and any number of rows.