SQL查询--内连接、外连接、自连接查询

比眉伴天荒 2023-10-12 08:02 54阅读 0赞

先创建2个表：学生表和教师表

![1158674-20190828214715500-942897350.png][] ![1158674-20190828214735569-188275876.png][]

### 1.内连接： ###

在每个表中找出符合条件的共有记录。[x inner join y on...] 
     
    第一种写法：只用where
    SELECT t.TEACHER_NAME,s.STUDENT_NAME FROM teacher t,student s WHERE t.ID=s.TEACHER_ID
    
    第二种写法：使用 join..on..
    SELECT t.TEACHER_NAME,s.STUDENT_NAME FROM teacher t JOIN student s ON t.ID=s.TEACHER_ID
    
    第三种写法：使用inner join .. on..
    SELECT t.TEACHER_NAME,s.STUDENT_NAME FROM teacher t INNER JOIN student s ON t.ID=s.TEACHER_ID

### 2.外连接：外连接有三种方式：左连接，右连接和全连接 ###

**(1)左连接：**根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配，如果找不到与左表匹配的，用null表示 \[x left \[outer\] join y on...\]

第一种写法：left join .. on ..
    SELECT t.TEACHER_NAME,s.STUDENT_NAME FROM teacher t LEFT JOIN student s ON t.ID=s.TEACHER_ID
    
    第二种写法：left outer join .. on ..
    SELECT t.TEACHER_NAME,s.STUDENT_NAME FROM teacher t LEFT OUTER JOIN student s ON t.ID=s.TEACHER_ID

![1158674-20190828215403394-843172733.png][]

**(2)右连接：**根据右表的记录，在被连接的左表中找出符合条件的记录与之匹配，如果找不到匹配的，用null填充 \[x right \[outer\] join y on...\]

第一种写法：right join .. on ..
    SELECT t.TEACHER_NAME,s.STUDENT_NAME FROM teacher t RIGHT JOIN student s ON t.ID=s.TEACHER_ID
    
    第二种写法：right outer join .. on ..
    SELECT t.TEACHER_NAME,s.STUDENT_NAME FROM teacher t RIGHT OUTER JOIN student s ON t.ID=s.TEACHER_ID

![1158674-20190828215520682-1467773910.png][]

**(3)全连接：**返回符合条件的所有表的记录，没有与之匹配的，用null表示（结果是左连接和右连接的并集）

第一种写法：（full join .. on ..）
    select t.teacher_name, s.student_name from teacher t full join student s on t.id = s.teacher_id;
    
    第二种写法：（full outer join .. on）
    select t.teacher_name, s.student_name from teacher t full outer join student s on t.id = s.teacher_id;

**注意：**Oracle数据库支持full join，mysql是不支持full join的

--------------------

如果直接查询2张表，将得到一个笛卡尔积，两个表的乘积 select \* from teacher,student;

![1158674-20190828220322513-1849837409.png][]

关于笛卡尔积的说明，请参考博客：[https://blog.csdn.net/csdn\_hklm/article/details/78394412][https_blog.csdn.net_csdn_hklm_article_details_78394412]

转载于:https://www.cnblogs.com/hanmk/p/11426747.html

[1158674-20190828214715500-942897350.png]: https://img2018.cnblogs.com/blog/1158674/201908/1158674-20190828214715500-942897350.png
[1158674-20190828214735569-188275876.png]: https://img2018.cnblogs.com/blog/1158674/201908/1158674-20190828214735569-188275876.png
[1158674-20190828215403394-843172733.png]: https://img2018.cnblogs.com/blog/1158674/201908/1158674-20190828215403394-843172733.png
[1158674-20190828215520682-1467773910.png]: https://img2018.cnblogs.com/blog/1158674/201908/1158674-20190828215520682-1467773910.png
[1158674-20190828220322513-1849837409.png]: https://img2018.cnblogs.com/blog/1158674/201908/1158674-20190828220322513-1849837409.png
[https_blog.csdn.net_csdn_hklm_article_details_78394412]: https://blog.csdn.net/csdn_hklm/article/details/78394412