mysql 中left join时 on、and、where区别

缺乏、安全感 2024-03-22 13:06 39阅读 0赞

### **1、准备两张表student与class表** ###

*  **student**

![9956d23f7b8b4efdbb0043042f000335.png][]

*  **class**

![dd37b7a8a1af4f09be553e1932e8acef.png][]

### 2、left join on左连接 ###

select * 
    from 
    student s
    left join class c
    on s.classId = c.id

左表数据全部显示，关联到的右表数据显示，没有显示null

![8005ed700de64b6aa0b9eb19ad2e0fc0.png][]

### 3、**left join on ... and对左表student进行条件筛选** ###

select * 
    from 
    student s
    left join class c
    on s.classId = c.id
    and s.`name` = '王五'

首先总体的行数并没有影响，其次不符合左表（student表）的条件，右表（class表）显示为null

![720af7f0066045c7b9609d7789194237.png][]

### 4、**left join on ... and对右表class进行条件筛选** ###

select * 
    from 
    student s
    left join class c
    on s.classId = c.id
    and c.`name` = '1班'

首先总体行数依旧没有影响，其次不符合右表的筛选条件，右表（class表）显示为null

![6ab6866d540e4f708ecd71ca6493c21c.png][]

### 5、**left join on ... where对左表student进行条件筛选** ###

select * 
    from 
    student s
    left join class c
    on s.classId = c.id
    where s.`name` = '王五'

过滤掉了student表和class表两个表的不满足的条件的行，从而只留下了一行结果

![0626ac3a5c8c49e8a47b27ed534e91a4.png][]

### 6、**left join on ... where对右表class进行条件筛选** ###

select * 
    from 
    student s
    left join class c
    on s.classId = c.id
    where c.`name` = '1班'

和上面的相同，都是过滤掉两个表不满足的行

![9980659b2f964aee906465740c66ea16.png][]

### 7、总结 ###

*  on关键字是对left join的右表进行条件过滤，但是依旧会返回左表的所有内容，右表不满足on条件都置为null
 *  and关键字，不管是左表还是右表的条件，左表的内容依旧不变，不符合and筛选条件的右表置为null。
 *  where关键字，不管是左表还是右表，只要不满足where筛选条件的两个表都会过滤掉。

[9956d23f7b8b4efdbb0043042f000335.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/f695351eb79a46dd9fc0c3c7cfd74d42.png
[dd37b7a8a1af4f09be553e1932e8acef.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/365729b3e8124767bbf47e2c7d8d6dc7.png
[8005ed700de64b6aa0b9eb19ad2e0fc0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/7eea1dc024b74b18b4b72069fde29a8a.png
[720af7f0066045c7b9609d7789194237.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/e89565fdb0e941a0a6412ab0f09f001b.png
[6ab6866d540e4f708ecd71ca6493c21c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/db646f65f4dc4e8eb82dbb3a9724f80d.png
[0626ac3a5c8c49e8a47b27ed534e91a4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/5d68e003fec147fbb2036f80e86d63cd.png
[9980659b2f964aee906465740c66ea16.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/eb45e8e2181145ef8c45d4c0ed4a77c0.png