mysql查询（四）

查询存在" A001 "课程但可能不存在" A002 "课程的情况(不存在时显示为 null

成绩表为：ex_chengjibiao_95

（1）查询A001

命令：SELECT * from ex_chengjibiao_95 WHERE CId='A001'

 

（2）查询A002

命令：SELECT * from ex_chengjibiao_95 WHERE CId='A002'

 

（3）满足存在A001，但是未必存在

         满足A001方向结果，未必满足A002方向结果

命令：SELECT a.Sid,a.Cid,a.score,b.Cid,b.score from

(SELECT * from ex_chengjibiao_95 WHERE Cid='A002')as a

left join

(SELECT * from ex_chengjibiao_95 WHERE CId='A001')as b

ON a.Sid=b.Sid

 

（4）对比，满足A002，未必满足A001方向结果

命令：SELECT a.Sid,a.Cid,a.score,b.Cid,b.score from

(SELECT * from ex_chengjibiao_95 WHERE Cid='A002')as a

right join

(SELECT * from ex_chengjibiao_95 WHERE CId='A001')as b

ON a.Sid=b.Sid

left join（左连接） ，满足左边条件，右边可能为空

right join（右连接），满足右边条件，左边可能为空