使用left join连接带NULL值数据表案例

来源：互联网发布：上海行知教育怎么样编辑：程序博客网时间：2024/06/06 00:14

insert into t_user values(1,'use1','123','user1',1,0,'test@qq.com',0,now());insert into t_user values(2,'use2','123','user2',1,0,'test@qq.com',0,now());insert into t_message values(1,'msg11','content11',now(),1);insert into t_message values(2,'msg12','content12',now(),1);insert into t_message values(3,'msg21','content21',now(),1);insert into t_message values(4,'msg22','content22',now(),1);insert into t_attachment values (1,'attach1','attach1','zip',now(),1);insert into t_attachment values (2,'attach2','attach1','zip',now(),1);select t_message.m_id,a_id from t_message,t_attachment where t_message.m_id = t_attachment.m_id;select t_message.u_id,m_id from t_user,t_message where t_message.u_id = t_user.u_id;#left join可以满足你的要求，右侧对附件表的连接，没有附件的话会补NULLselect * from ((select t_message.u_id,m_id from t_user,t_message where t_message.u_id = t_user.u_id) as a left join (select t_message.m_id,a_id from t_message,t_attachment where t_message.m_id = t_attachment.m_id) as b on a.m_id = b.m_id);

结果集：
u_id | m_id | m_id | a_id
——+——+——+——
1 | 1 | 1 | 1
1 | 1 | 1 | 2
1 | 2 | |

1 | 3 | |

1 | 4 | |

(5 rows)

0 0