Skip to content
Advertisement

What is Difference Between AND,OR operator in MYSQL ,While Retrieving the Rows?

Are there difference between these queries and how they execute?

SELECT   customername, country, creditLimit
FROM   customers
WHERE (country = 'USA' OR country = 'France') AND creditlimit > 100000;
SELECT    customername, country, creditLimit
FROM    customers
WHERE country = 'USA' OR country = 'France' AND creditlimit > 10000;

Advertisement

Answer

It’s to do with intersecting truth tables.

In the first query the or is inside brackets so it will execute first and return true if the country is either USA or France. Then the And will execute and compare the result of the OR to the result of creditlimit > 100000.

In the second query operator precedence takes over, so whether the Or or the And is executed first. According to this site https://www.mysqltutorial.org/mysql-or/

Operator precedence When you use more than one logical operator in an expression, MySQL always evaluates the OR operators after the AND operators. This is called operator precedence which determines the order of evaluation of the operators. MySQL evaluates the operator with higher precedence first.

This means that in this query the database will check to see if the Country is France and the creditlimit > 10000 before then running the OR statement between the result and whether the country is USA.

ETA: in plain english the second query, will return all results where the country is USA regardless of the credit limit, it will also return any results where the country is france and the credit limit > 10000.

6 People found this is helpful
Advertisement