SQL中join的用法解析（inner join、full out join、left join）

墨蓝 2022-03-29 13:26 653阅读 0赞

话不多说，先上个对于各种join情况的总结图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70]

例如有如下关系的数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 1]

1、inner join

![在这里插入图片描述][20190108192238219.png]

select * from Table_a
    join Table_b on Table_a.id_a = Table_b.id_b

![在这里插入图片描述][20190108191637548.png]

2.1、full outer join等于full join

![在这里插入图片描述][20190108192247356.png]

select * from Table_a
    full join Table_b
    on Table_a.id_a = Table_b.id_b

![在这里插入图片描述][20190108191758506.png]

2.2、full outer join but a.Key is null or b.Key is null

![在这里插入图片描述][20190108192256791.png]

select * from Table_a
    full outer join Table_b
    on Table_a.id_a = Table_b.id_b
    where Table_a.id_a is null or Table_b.id_b is null

![在这里插入图片描述][20190108191845768.png]

3.1、left outer join等于left join

![在这里插入图片描述][20190108192307255.png]

select * from Table_a
    left join Table_b on Table_a.id_a = Table_b.id_b

![在这里插入图片描述][20190108191935305.png]

3.2、left outer join but b.Key is null

![在这里插入图片描述][20190108192318660.png]

select * from Table_a
    left outer join Table_b on Table_a.id_a = Table_b.id_b
    where Table_b.id_b is null

![在这里插入图片描述][20190108192019631.png]

1.  笛卡尔积

select * from Table_a ,Table_b

![在这里插入图片描述][20190108192138330.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70]: /images/20220329/aa9a67cd95b149a6af2961cf1dcfcd9c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01lZ3VzdGFzX0pKQw_size_16_color_FFFFFF_t_70 1]: /images/20220329/f1d683d0f63d4906aa7feafd26151e87.png
[20190108192238219.png]: /images/20220329/ff9fab2131d443ca9a527ec0b07d33ea.png
[20190108191637548.png]: /images/20220329/952199ea937c42ceb6b575903d6b146b.png
[20190108192247356.png]: /images/20220329/1d4c6a1cf62a46c4b01edfe0f1fc5548.png
[20190108191758506.png]: /images/20220329/27b9ccfed18b41c680a707ca834335e2.png
[20190108192256791.png]: /images/20220329/9c905082bd6f4faf9d84f03613df5fbf.png
[20190108191845768.png]: /images/20220329/a6aecfb8276544ac9168c1f9abcb6ab9.png
[20190108192307255.png]: /images/20220329/562ab1cf3e824d5186b5e0d8970c58a3.png
[20190108191935305.png]: /images/20220329/1d63901cd31441c2aff7587e9338e876.png
[20190108192318660.png]: /images/20220329/d35623f8f6774d7396186e5218cf01b6.png
[20190108192019631.png]: /images/20220329/9559631294034d919c179dff6e998929.png
[20190108192138330.png]: /images/20220329/54df02b8d02d4ceab86e676a9a3fc166.png