SQL JOIN ON 与 WHERE中过滤条件对比

2021-03-19 好记性不如烂笔头 0 条评论

在使用SQL JOIN的时候发现在使用 ON 和 WHERE 筛选数据时结果不一样,所以创了两个表测试下.

创建测试表t3,t4,并插入数据

    CREATE TABLE t3(
     rn int,
     zjhm VARCHAR(50),
     `name` VARCHAR(50)
    )
    
    CREATE TABLE t4(
    rn int,
     zjhm VARCHAR(50),
     `phone` VARCHAR(50)
    )
    
    TRUNCATE TABLE t3;
    TRUNCATE TABLE t4;
    INSERT t3(rn,zjhm,name) VALUES(3,'101','A1013');
    INSERT t3(rn,zjhm,name) VALUES(2,'101','A1012');
    INSERT t3(rn,zjhm,name) VALUES(1,'101','A1011');
    INSERT t3(rn,zjhm,name) VALUES(2,'102','A1022');
    INSERT t3(rn,zjhm,name) VALUES(1,'102','A1021');
    INSERT t3(rn,zjhm,name) VALUES(1,'103','A1031');
    
    INSERT t4(rn,zjhm,phone) VALUES(1,'101','10011');
    INSERT t4(rn,zjhm,phone) VALUES(2,'101','10012');
    INSERT t4(rn,zjhm,phone) VALUES(1,'102','1002');
    INSERT t4(rn,zjhm,phone) VALUES(1,'103','1003');

先简单JOIN:
-- sql1