MySql Operators AND With OR

While using MySql OR and MySql AND operators, sometimes we face problems for not getting the desired results. Why ? May be because using OR with AND operators is complicated ? or otherwise buggy ? But its not like that. If we want to use MySql operators AND and OR, then we have to make sure what we are assuming is what actually MySql Query is executing.

For example, we have a table tbl_products, with the fields product_name and amount, and we want to fetch the results where:

• Amount contains values lesser than 50
• Product contains values ‘Nokia’ or ‘Samsung’

So, if we write the follwing query:

SELECT * FROM tbl_products
WHERE amount < 50
AND product_name = 'Nokia'
OR product_name = 'Samsung';

then our results may not match, becuase MySql will be taking the 1st two conditions

WHERE amount < 50
AND product_name = 'Nokia'

to be treated as one, and the other condition as second. Which means, it will match the Nokia products having amount less than 50, or, it will match the Samsung products only, with no amount check. Whereas, our need was to get the amount less than 50 for any Nokia or Samsung products.

So the following query will take the accurate results:

SELECT * FROM tbl_products
WHERE amount < 50
AND
(
	product_name = 'Nokia' OR product_name = 'Samsung'
);

or this query could also be written as:

SELECT * FROM tbl_products
WHERE
(
	product_name = 'Nokia' OR product_name = 'Samsung'
)
AND amount < 50;

Hope this will help you in using MySql AND and OR operators.