创建数据库

/*
 Navicat Premium Data Transfer

 Source Server         : master-test
 Source Server Type    : MySQL
 Source Server Version : 50741
 Source Host           : localhost:3340
 Source Schema         : test_left

 Target Server Type    : MySQL
 Target Server Version : 50741
 File Encoding         : 65001

 Date: 16/08/2023 17:00:44
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for u1
-- ----------------------------
DROP TABLE IF EXISTS `u1`;
CREATE TABLE `u1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of u1
-- ----------------------------
BEGIN;
INSERT INTO `u1` (`id`, `name`) VALUES (1, 'a');
INSERT INTO `u1` (`id`, `name`) VALUES (2, 'b');
INSERT INTO `u1` (`id`, `name`) VALUES (3, 'c');
COMMIT;

-- ----------------------------
-- Table structure for u2
-- ----------------------------
DROP TABLE IF EXISTS `u2`;
CREATE TABLE `u2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of u2
-- ----------------------------
BEGIN;
INSERT INTO `u2` (`id`, `name`) VALUES (2, 'bb');
INSERT INTO `u2` (`id`, `name`) VALUES (2, 'cc');
INSERT INTO `u2` (`id`, `name`) VALUES (7, 'yy');
COMMIT;

-- ----------------------------
-- Table structure for u3
-- ----------------------------
DROP TABLE IF EXISTS `u3`;
CREATE TABLE `u3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of u3
-- ----------------------------
BEGIN;
INSERT INTO `u3` (`id`, `name`) VALUES (2, 'apple');
INSERT INTO `u3` (`id`, `name`) VALUES (1, 'banana');
INSERT INTO `u3` (`id`, `name`) VALUES (9, 'lizi');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

正常查询表的记录

u1:

u2:

u3:

left join on

SELECT a.id,a.`name`,c.id,c.`name` from u1 a LEFT JOIN u3 c on a.id=c.id; 查询的是以左表为基础的全集加上右表中符合条件的数据：

left join on and

SELECT a.id,a.`name`,c.id,c.`name` from u1 a LEFT JOIN u3 c on a.id=c.id and a.`name`='b'; 查询的是以左表为基础的全集加上右表中符合条件的数据，然后对结果集做了过滤处理，只保留a.name=b条件下的B表数据，其余B表数据不保存,a表全部数据不影响：

left join on where and

查询的是以左表为基础的全集加上右表中符合条件的数据,在查询的时候对整个结果集进行处理，只查询满足条件的结果集数据，a\b表结果都有影响，都要满足where中的条件。
SELECT a.id,a.`name`,c.id,c.`name` from u1 a LEFT JOIN u3 c on a.id=c.id where a.`name`='b';