**左连接:**左表内容全部输出,右表只输出on后条件与左表满足的部分,如果右表中没有匹配,则依然会有左表的记录,右表字段用null填充。
SQL语句:
select * from table_a left join table_b on table_a.id=table_b.id;
例题:
Mysql中表student_table(id,name,birth,sex),插入如下记录:
('1001' , '' , '2000-01-01' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , NULL , '2001-12-01' , '女');
('1006' , '张三' , '2001-12-02' , '女');
执行:
select t1.name from
(select * from student_table where sex = '女')t1
left join
(select * from student_table where sex = '男')t2
on t1.name = t2.name;
的结果行数是()?
答案:2
执行t1.name=t2.name后,满足条件只有张三,但是左连接坐标要全部输出,因此NULL也要输出
输出结果为:
('1005' , NULL , '2001-12-01' , '女',NULL,NULL,NULL);
('1006' , '张三' , '2001-12-02' , '女','1004','2000-08-06' , '男');
若出现笛卡尔积情况:
从左表中逐行取数据,与右表中数据做匹配
笛卡尔积连接表的行数=两表符合条件的行数乘积
例题:Mysql中表student_table(id,name,birth,sex),插入如下记录:
('1004' , '张三' ,'2000-08-06' , '男');
('1009' , '李四', '2000-01-01', '男');
('1010' , '李四', '2001-01-01', '男');
('1006' , '王五', '2000-08-06' , '女');
('1008' , '张三', '2002-12-01', '女');
('1012' , '张三', '2001-12-01', '女');
('1011' , '李四', '2002-08-06' , '女');
执行
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
left join
(select * from student_table where sex = '女')t2
on t1.name = t2.name ;
的结果行数是()?
答案:4
男表全部输出,逐行与女表中数据做匹配
即:12+21=4
1(张三男)*2(张三女)+2(李四男)*1(李四女)=4