通过一段时间的学习,将SQL join的三种用法进行总结
以下两个表为基础表:
表A
ID name
01 joy
02 kK
03 lily
04 Mike
表B
SID age
01 10
03 12
04 11
05 9
-------------------------------------------------------------------------------------------------------------------------------------------------------
1.inner join:只返回两个表中符合on条件的行
select A.ID,A.name,B.age from A inner join B on A.ID=B.SID----返回表如下所示:
ID name age
01 joy 10
03 Lily 12
04 Mike 11
-------------------------------------------------------------------------------------------------------------------------------------------------------
2.left join:以left join前面的表为主表,把left join后面的表符合on条件的行加到主表中,如果没有符合on条件的内容,则显示为NULL
select ID,name,age from A left join B on A.ID=B.SID----返回表如下所示:
ID name age
01 joy 10
02 kK null
03 lily 12
04 Mike 11
-------------------------------------------------------------------------------------------------------------------------------------------------------
3.right join:以right join后面的表为主表,把right join前面的表符合on条件的行加到主表中,如果没有符合on条件的内容,则显示为NULL
select ID,name,age from A right join B on A.ID=B.SID----返回表如下所示:
ID name age
01 joy 10
03 Lily 12
04 Mike 11
null null 9
-------------------------------------------------------------------------------------------------------------------------------------------------------
以下两个表为基础表:
表A
ID name
01 joy
02 kK
03 lily
04 Mike
表B
SID age
01 10
03 12
04 11
05 9
-------------------------------------------------------------------------------------------------------------------------------------------------------
1.inner join:只返回两个表中符合on条件的行
select A.ID,A.name,B.age from A inner join B on A.ID=B.SID----返回表如下所示:
ID name age
01 joy 10
03 Lily 12
04 Mike 11
-------------------------------------------------------------------------------------------------------------------------------------------------------
2.left join:以left join前面的表为主表,把left join后面的表符合on条件的行加到主表中,如果没有符合on条件的内容,则显示为NULL
select ID,name,age from A left join B on A.ID=B.SID----返回表如下所示:
ID name age
01 joy 10
02 kK null
03 lily 12
04 Mike 11
-------------------------------------------------------------------------------------------------------------------------------------------------------
3.right join:以right join后面的表为主表,把right join前面的表符合on条件的行加到主表中,如果没有符合on条件的内容,则显示为NULL
select ID,name,age from A right join B on A.ID=B.SID----返回表如下所示:
ID name age
01 joy 10
03 Lily 12
04 Mike 11
null null 9
-------------------------------------------------------------------------------------------------------------------------------------------------------