使用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 | |

(5 rows)