MYSQL question - AND or OR?

Question

Which is a better way to select ans and quest from the table?

SELECT * FROM tablename WHERE option='ans' OR option='quest'";

OR

SELECT * FROM tablename WHERE option='ans' AND option='quest'";

Thanks so much!

Answer 1

Your second statement is not going to return any results. A record cannot have option=ans and option=quest at the same time.

Answer 2

It's not a question of a 'better' way - only the first one works. Even though you want option=ans and option=quest in your results set, the WHERE clause is executed once per row. So you're telling MySQL "give me a row where option=quest and option=ans" i.e. option is two values at once, which is impossible. You actually want to get rows where either is true, which is why you use OR.

I think this reads better:

SELECT * FROM tablename WHERE option IN('ans','quest');

Answer 3

If the rows that represent question have option set to 'quest' and rows with answer have option set to 'ans' then you should use option='ans' OR option='quest'";. Also a row cannot represent both question and answer so using AND will not select any rows.

Answer 4

This select will return all rows whos options is ans or quest

SELECT * FROM tablename WHERE option='ans' OR option='quest'";

This select on the other hand will not return any rows since a column has only one of those values

SELECT * FROM tablename WHERE option='ans' AND option='quest'";

Answer 5

Use this if you want your search to return both answers and questions:

SELECT * FROM tablename WHERE option='ans' OR option='quest';

It can also be written:

SELECT * FROM tablename WHERE option in ('ans','quest');