SQL join leftjoin right join
来源:互联网 发布:电脑软件职业技术培训 编辑:程序博客网 时间:2024/05/29 10:12
有两张表student和team表
Student
id card_id name age team_id
402881e64b5d8b54014b5d8b570f0001 s3 zhangsan 25 402881e64b5d8b54014b5d8b56f10000
402881e64b5d8b54014b5d8b570f0002 s4 zhangsan 25 402881e64b5d8b54014b5d8b56f10000
402881e64b5d8b54014b5d8b570f0003 s5 zhangsan 25 402881e64b5d8b54014b5d8b56f10000
402881e64b5d8b54014b5d8b570f0004 s6 zhangsan 25 402881e64b5d8b54014b5d8b56f10000
402881e64b5d8b54014b5d8b570f0005 s1 zhangsan 25 402881e64b5d8b54014b5d8b56f10000
402881e64b5d8b54014b5d8b570f0006 s2 zhangsan 25 402881e64b5d8b54014b5d8b56f10000
Team
id name
402881e64b5988cd014b5988cf700000 team1
402881e64b5d8b54014b5d8b56f10000 team2
(1) 左外连接
select*from student_3leftjoin teamON team.id = student_3.team_id ;
id card_id name age team_id id1 name1
402881e64b5d8b54014b5d8b570f0001 s3 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0002 s4 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0003 s5 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0004 s6 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0005 s1 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0006 s2 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
以左边的表为主表。主表的所有信息都会出现在结果集中。而右表team只显示了符合条件(team.id = student_3.team_id)的记录。同理如果左表中包含了一条记录,不符合on关键字以后的条件,左表记录也会出现在结果集中,而此时右表为NULL
(2)右外连接
id card_id name age team_id id1 name1
402881e64b5d8b54014b5d8b570f0001 s3 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0002 s4 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0003 s5 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0004 s6 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0005 s1 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0006 s2 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
NULL 402881e64b5988cd014b5988cf700000 team1
右外连接以右表作为主表,主表的所有记录都会出现在结果集中。左表显示了符合on关键字的条目。如果不存在,则为null。
(3)内连接
内连接有两种
第一种不指定任何限定条件,查询结果为两个表的笛卡尔积,通常这种查询没有什么意义
select*from student_3innerjoin team
id card_id name age team_id id1 name1
402881e64b5d8b54014b5d8b570f0001 s3 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5988cd014b5988cf700000 team1
402881e64b5d8b54014b5d8b570f0001 s3 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0002 s4 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5988cd014b5988cf700000 team1
402881e64b5d8b54014b5d8b570f0002 s4 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0003 s5 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5988cd014b5988cf700000 team1
402881e64b5d8b54014b5d8b570f0003 s5 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0004 s6 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5988cd014b5988cf700000 team1
402881e64b5d8b54014b5d8b570f0004 s6 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0005 s1 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5988cd014b5988cf700000 team1
402881e64b5d8b54014b5d8b570f0005 s1 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0006 s2 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5988cd014b5988cf700000 team1
402881e64b5d8b54014b5d8b570f0006 s2 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
第二种带有限定条件,这种情况返回的是满足限定条件的条目。
select*from student_3innerjoin teamon team.id = student_3.team_id
id card_id name age team_id id1 name1
402881e64b5d8b54014b5d8b570f0001 s3 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0002 s4 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0003 s5 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0004 s6 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0005 s1 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
402881e64b5d8b54014b5d8b570f0006 s2 zhangsan 25 402881e64b5d8b54014b5d8b56f10000 402881e64b5d8b54014b5d8b56f10000 team2
- SQL join leftjoin right join
- SQL leftjoin 和 right join的使用
- SQl inner join,left join,right join
- sql中的join、left join、right join
- SQL Join/Left Join /Right Join...
- SQL RIGHT JOIN 关键字
- SQL RIGHT JOIN 关键字
- SQL RIGHT JOIN
- SQL RIGHT JOIN
- SQL RIGHT JOIN 关键字
- SQL语句RIGHT JOIN
- SQL RIGHT JOIN 关键字
- SQL RIGHT JOIN 关键字
- SQL RIGHT JOIN
- sql的inner join/left join/right join/full join
- SQL join,left join ,right join,full join
- SQL-------left join、right join 、full join、inner join
- SQL 中join、inner join、left join、right join、
- c++ primer plus的学习
- 8a 计时器初次体验
- <总结>delphi WebBrowser控件的使用中出现的bug
- Problem 043——UVa 1593 - Alignment of Code
- 初学C++ 的50忠告!看到就发在自己的博客里提醒自己!
- SQL join leftjoin right join
- 有趣的Java 9
- 设计模式介绍1:单例模式(Singleton)
- 设计模式介绍:概述
- IOS-分析JSON、XML的区别?JSON、XML解析方式的底层是如何处理的?
- 数据库安全权限控制管理思想
- matlab知识集锦(3)
- mysql数据库sql语句调优、
- Error[Li005]: no definition for "__iar_program_start"