IN operator with Inner query SQL

Question

I have this query

SELECT *
FROM `posts`
WHERE `language` IN ( SELECT `possible_languages` FROM `users` WHERE user_id = ? );

In the table posts: languages can be either en or ar
In the table users: possible_languages can be either 'en', 'ar' or 'en','ar'
When the selected value is 'en','ar' the query doesn't work
I want it to be able to select Posts that are either en or ar IN ('en','ar')

Answer 1

You cannot use in with such lists. You can do this with exists:

SELECT p.*
FROM `posts` p
WHERE exists (select 1
              from `users` u
              where u.user_id = ? and find_in_set(p.language, u.possible_languages)
             )

This will be more efficient if you have an index on users(user_id, possible_languages).

Answer 2

May be more efficient as a join

SELECT posts.*
FROM `posts`, `users`
WHERE find_in_set(`language`,`possible_languages`)
AND user_id = ?

A more efficient query for this however may be:

SELECT posts.*
  FROM posts,users
 WHERE (language=possible_languages OR possible_languages='ar,en')
   AND user_id=?

Assuming the values in language and possible_languages are only as you specified. In this case an index on language in posts is useful.