Q:

How to get rows by filtering multiple columns which contain values in MySQL?

I have a MySQL table which is named fruits:

I want to select rows from the table where the columns fruit1, fruit2, and fruit3 only contain values in the set [apple, pear, melon] i.e.

only have apple
only have pear
only have melon
only have both apple and pear
only have both apple and melon
only have both pear and melon
only have apple, pear and melon

For my sample data, the rows will be 1, 5, 8.
I have tried this query:
SELECT * FROM fruits WHERE 'apple' in (fruit1, fruit2, fruit3)

which gave me all the rows that contain apple in one of the fruit columns. However I'm not sure how to filter on multiple columns.

A:

I've worked on the assumption that blank fruit values in your table are NULL. In that case this query will give you the desired result:
SELECT *
FROM fruits
WHERE (fruit1 IN ('apple', 'melon', 'pear') OR fruit1 IS NULL)
  AND (fruit2 IN ('apple', 'melon', 'pear') OR fruit2 IS NULL)
  AND (fruit3 IN ('apple', 'melon', 'pear') OR fruit3 IS NULL)

Output
id  fruit1  fruit2  fruit3
1   apple       
5   melon           pear
8   apple           melon

If the blank values are just empty strings, change the query to 
SELECT *
FROM fruits
WHERE (fruit1 IN ('apple', 'melon', 'pear') OR fruit1 = '')
  AND (fruit2 IN ('apple', 'melon', 'pear') OR fruit2 = '')
  AND (fruit3 IN ('apple', 'melon', 'pear') OR fruit3 = '')

Demo on dbfiddle

