Specific use of MySQL operators (and, or, in, not)

1. Introduction
2. Main text

2.1 and Operator
2.2 or Operator
2.3 in Operator
2.4 not Operator
2.5 Operator Order

1. Introduction

When using the where clause to filter query data in MySQL, it is often necessary to meet multiple filtering conditions at the same time, or to meet one of the multiple filtering conditions. At this time, we can use operators to connect the where clauses.

The functions of several operators:

Operators	effect
and	And, the conditions in the where clause must be met at the same time
or	Or, only one condition in multiple where clauses needs to be matched
in	Used to specify the scope of the where clause query
not	Not, usually used with in, between and, exists, to indicate negation

2. Main text

First, prepare a User table. The DDL and table data are as follows and can be copied and used directly.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'User name',
  `age` int(11) NOT NULL COMMENT 'Age',
  `sex` smallint(6) NOT NULL COMMENT 'Gender',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '李子８', 18, 1);
INSERT INTO `user` VALUES (2, '张三', 22, 1);
INSERT INTO `user` VALUES (3, '李四', 38, 1);
INSERT INTO `user` VALUES (4, '王五', 25, 1);
INSERT INTO `user` VALUES (5, '六麻子', 13, 0);
INSERT INTO `user` VALUES (6, '田七', 37, 1);
INSERT INTO `user` VALUES (7, 'Thank you', 18, 0);
 
SET FOREIGN_KEY_CHECKS = 1;

The initial order of the data is as follows:

mysql> select * from user;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 0 |
+----+--------+-----+-----+
7 rows in set (0.00 sec)

2.1 and Operator

When the query needs to satisfy the conditions in the where clause at the same time, you can use the and operator. The and conditions are in an and relationship.

need:
Query users whose age is 18 and whose gender is male (note: sex=1 represents male)
Statement:

mysql> select * from user where age = 18 and sex = 1;

result:

+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
+----+--------+-----+-----+
1 row in set (0.00 sec)

At this point, you can see that only users who meet both age=18 and sex=1 are queried. By analogy, multiple ands can exist at the same time. For example, if you need to search for **name=李子柒** based on the above, you only need to follow it with another and operator.

mysql> select * from user where age = 18 and sex =1 and name = '李子柒';
Empty set (0.00 sec)

2.2 or Operator

Different from and, or only needs to satisfy one of the multiple where conditions, not all of them. The conditions are in an or relationship.

need:
Query users whose age=18 or gender is male (note: sex=1 represents male)
Statement:

mysql> select * from user where age = 18 or sex = 1;

result:

+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 0 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)

At this point, you can see that all users who meet age=18 or sex=1 are found. The same or operator can also be applied to multiple where clauses at the same time.

2.3 in Operator

The in operator is used to specify the query scope of the where clause. It means inclusion, and it can be implemented using multiple or operators.

need:
Query the user information whose name is equal to Zhang San, Li Si, and Wang Wu.
Statement:
Using the or Operator

mysql> select * from user where name = '张三' or name = '李四' or name = '王五';
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
+----+------+-----+-----+
3 rows in set (0.00 sec)

Using the in Operator

mysql> select * from user where name in ('张三', '李四', '王五');
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
+----+------+-----+-----+
3 rows in set (0.00 sec)

The above requirements can be achieved by using the or operator and the in operator, but the in operator obviously makes the SQL statement concise.

2.4 not Operator

When we need to query whether a value is not within a certain range or does not exist, we can use the not operator. The not operator is not used alone, it is often used with the in operator, like operator, between and, exists, etc.

not in
need:
Query user information whose names are not equal to Zhang San, Li Si, or Wang Wu.
Statement:

mysql> select * from user where name not in ('张三', '李四', '王五');
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 0 |
+----+--------+-----+-----+
4 rows in set (0.00 sec)

not like
need:
Query the user whose name does not start with "lizi":

mysql> select * from user where name not like '李子%';
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 0 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)

not between and
need:
To query users whose age is not between 20 and 30:

mysql> select * from user where age not between 20 and 30;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 3 | Li Si | 38 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 0 |
+----+--------+-----+-----+
5 rows in set (0.00 sec)

not exists

The not exists table is used in the same way as exists and is used to determine whether the result of the current where clause should be returned. not exists and exists act on a subquery and return true and false to the parent;
Example syntax:

SELECT ... FROM table WHERE EXISTS (subquery)
SELECT ... FROM table WHERE NOT EXISTS (subquery)

To demonstrate the effect, we create a simple order table, whose table creation statement and data are as follows:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Order number',
  `user_id` bigint(20) NULL DEFAULT NULL COMMENT 'user id',
  `price` decimal(10, 2) NULL DEFAULT NULL COMMENT 'amount',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT 'Creation date',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (1, 'DD-20211110-000001', 1, 250.00, '2021-11-10 22:37:19');
 
SET FOREIGN_KEY_CHECKS = 1;

Note: Since order is a MySQL keyword, it is not recommended to name the table directly order when creating it. I name it order here to explain how to solve this problem.

mysql> select * from `order`;
+----+--------------------+---------+--------+---------------------+
| id | number | user_id | price | create_date |
+----+--------------------+---------+--------+---------------------+
| 1 | DD-20211110-000001 | 1 | 250.00 | 2021-11-10 22:37:19 |
+----+--------------------+---------+--------+---------------------+
1 row in set (0.00 sec)

If you look carefully, you can find that order is modified with `, so that MySQL will not parse it as a keyword. If you don't add MySQL, an exception will be thrown.

Back to the topic, we now use exists to query the requirements:
Query the user information of the order placed:

mysql> select * from user where exists(select id from `order` where user_id = user.id);
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
+----+--------+-----+-----+
1 row in set (0.00 sec)

At this time, if we want to query the user information who has not placed an order, we only need to use not exists.

mysql> select * from user where not exists (select id from `order` where user_id = user.id);
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 0 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)

2.5 Operator Order

Several operators are mentioned above, but in many cases multiple operators need to be used together. At this time, we need to pay attention to the order of the operators.

For example, the following requirements:
Query the user table for users who are older than 20 years old or male, and whose names are not equal to Zhang San.
Statement:

mysql> select * from user where age > 20 or sex = 1 and name != '张三';
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 6 | Tianqi | 37 | 1 |
+----+--------+-----+-----+
5 rows in set (0.00 sec)

At this time, it is found that the query return results actually include Zhang San. This is because and has a higher priority than or. The SQL parser at the bottom of MySQL parses the above SQL into sex = 1 and name != 'Zhang San' or age > 20; Zhang San is also found out because age > 20. To solve this problem, just use parentheses to enclose the or statement.

mysql> select * from user where (age > 20 or sex = 1) and name != '张三';
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 6 | Tianqi | 37 | 1 |
+----+--------+-----+-----+
4 rows in set (0.00 sec)

At this time, Zhang San is no longer included in the returned data of the query.

Therefore, when writing SQL, we can develop the habit of using brackets. By grouping operators in brackets, we can avoid the risk of errors caused by using the default order.

This concludes this article on the specific use of MySQL operators (and, or, in, not). For more relevant MySQL operator content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: