Solve the problem of combining AND and OR in MySQL

As shown below:

SELECT prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

The result returned by the above statement is not what we want.

analyze:

The reason lies in the order of calculation. SQL prioritizes AND operators before OR operators. When SQL sees the above WHERE clause, the operators are incorrectly combined because AND has a higher precedence in the calculation order.

The solution to this problem is to use parentheses to explicitly group corresponding operators.

Please see the following SELECT statement

 SELECT prod_name,prod_price
    FROM products

    WHERE ( vend_id = 1002 OR vend_id = 1003 ) AND prod_price >= 10;

Supplementary knowledge: Mysql | Combined where clause to filter data (AND, OR, IN, NOT)

MySQL allows the use of multiple where clauses, and the combination of where clauses allows the use of two methods: AND and OR clauses.

Operation symbols in the database: AND, OR, IN, NOT.

AND :

SELECT * FROM products WHERE products.vend_id = 1003 AND products.prod_price <= 10;

OR :

SELECT * FROM products WHERE products.vend_id = 1002 OR products.vend_id = 1003;

IN :

It is recommended not to use OR in clauses where IN can be used. IN has good performance and is easy to understand.

SELECT * FROM products WHERE products.vend_id IN (1002,1003);

NOT:

MySQL supports NOT only in the negation of IN, BETWEEN, and EXISTS clauses, which is different from most other databases that support various conditions.

SELECT * FROM products WHERE products.vend_id NOT IN (1002,1003);

Notice:

In a clause that contains both AND and OR, MySQL prioritizes AND operations. It is generally recommended to use () to determine the processing order and eliminate ambiguity.

for example:

SELECT * FROM products WHERE (products.vend_id= 1002 OR products.vend_id=1003) AND prod_price >= 10;

The above article on solving the problems caused by the combination of AND and OR in MySQL is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.