A brief analysis of the difference between and and where in MySQL connection query

1. Create a table

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `student` VALUES (1, '张三', 12);
INSERT INTO `student` VALUES (2, '李四', 12);
INSERT INTO `student` VALUES (3, '王五', 12);
INSERT INTO `student` VALUES (4, '赵六', 12);
INSERT INTO `student` VALUES (5, '孙七', 12);
INSERT INTO `student` VALUES (6, 'turtle', 12);

CREATE TABLE `grade` (
  `id` int(11) NOT NULL,
  `sid` int(11) NULL DEFAULT NULL,
  `grade` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `grade` VALUES (1, 1, 100);
INSERT INTO `grade` VALUES (2, 2, 80);
INSERT INTO `grade` VALUES (3, 3, 99);
INSERT INTO `grade` VALUES (4, 4, 66);

2. Inner join

There is no difference between and and where in inner joins. Both use the results after the join to perform conditional screening.

2.1 No conditions

2.2 and Condition

2.3 where condition

3. left join

3.1 No conditions

3.2 and Condition

In left join, the left table is fully matched for connection, and then AND is used for filtering; the left table data that does not meet the conditions is retained, and the right table data is null.

3.3 where condition

In left join, the left table is fully matched for connection, and then the where clause is used for filtering; only the data that meets the conditions is filtered.

4. right join

Same as left join in 3, except that the base tables are reversed.

5. Conclusion

• For all connections, use where to filter the data rows that meet the conditions after the connection, and only keep the data rows that meet the conditions;
• When left join is used, and the left table is used as the main table. All data in the left table is retained, and the data rows that do not meet the conditions are set to null in the right table.
• When right join is used, and the right table is used as the main table. All data in the right table is retained, and the left table data that does not meet the conditions is null;

This is the end of this article about the difference between and and where in MySQL connection query. For more relevant MySQL connection query and where 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!