Priority analysis of and or query in MySQL

Time:2021-12-31

This may be an easily overlooked problem. We should be clear about:
In mysql, and takes precedence over or. That is, without the restriction of parentheses (), the and statement is always executed first and then the or statement.
For example:

Select * from table where condition 1 and condition 2 or condition 3
Equivalent to
Select * from table where (condition1 and condition2) or condition3

Select * from table where condition 1 and condition 2 or condition 3 and condition 4
Equivalent to
Select * from table where (condition1 and condition2) or (condition3 and condition4)

Let’s have an in-depth understanding of some examples:

Test table data:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
 `author` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
 `price` decimal(10, 2) DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, 'PHP', 'mate', 21.00);
INSERT INTO `book` VALUES (2, 'JAVA', 'kaven', 23.00);
Insert into ` book ` values (3, 'Java advanced', 'loose', 45.00);
INSERT INTO `book` VALUES (4, 'GO', 'jim', 46.00);
Insert into ` book ` values (5, 'go design', 'JSON', 76.00);
Insert into ` book ` values (6, 'PHP advanced programming', 'bate', 67.00);
INSERT INTO `book` VALUES (7, 'Python', 'jim', 66.00);
Insert into ` book ` values (8, 'Python design', 'Mali', 54.00);
Insert into ` book ` values (9, 'go programming', 'Kaven', 86.00);
INSERT INTO `book` VALUES (11, 'Python3', 'jim', 55.00);

SET FOREIGN_KEY_CHECKS = 1;

Query method 1:


SELECT * FROM book WHERE author='jim' OR author='json' AND name='PHP';

The above query is equivalent to:


SELECT * FROM book WHERE author='jim' OR (author='json' AND name='PHP');

Then the above query results are well understood.

Query method 2:


SELECT * FROM book WHERE name='PHP' AND author='jim' OR author='json';

The above query is equivalent to:


SELECT * FROM book WHERE (name='PHP' AND author='jim') OR author='json';

Query method 3:


SELECT * FROM book WHERE name='GO' AND (author='jim' OR author='json');

This is easy to understand. Understand the priority of and or. These queries are not “confused”.

This is the end of this article on priority analysis of and or queries in MySQL. For more information about MySQL and or queries, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!