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

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可以满足你的要求,右侧对附件表的连接,没有附件的话会补NULL select * 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 | |
【使用left join连接带NULL值数据表案例】(5 rows)

    推荐阅读