mysql - 需要查询来检索与另一表连接的一个表中的所有行

Question

我有两个表，消息和用户。我想要一个从按摩表中检索所有行的查询，无论 user_id 字段 NULL 的值如何。

消息表:

id,      msg_date,       sms_text,  contact_id, user_id
'1',     '2019-04-09 ',  'Hello 1',    '1',       '1'
'2',     '2019-04-10',   'Hello 2',    '1',       NULL
'3',     '2019-04-11',   'Hello 3',    '1',       '1'

用户表:

# user_id, fullname
'1',       'Manish Sijaria'

如果我执行以下查询。它不会从消息表中返回行 2。

SELECT id, message_date , msg_from, msg_to, sms_text, contact_id, 
       message.user_id, user.fullname  
FROM message left join user on message.user_id=user.user_id 
                               and message.contact_id=1;

上述查询的结果:-

id,   message_date,   sms_text, contact_id, user_id, fullname
'1',  '2019-04-09',   'Hello 1',  '1',       '1',    'Manish Sijaria'
'3',  '2019-04-11 ',  'Hello 3',  '1',       '1',    'Manish Sijaria'

行 2 缺失，因为 user_id 为 NULL。

但我也希望消息表中包含 user_id=NULL 的行。我想要以下结果:-

id,   message_date,   sms_text, contact_id, user_id, fullname
'1',  '2019-04-09',   'Hello 1',  '1',       '1',     'Manish Sijaria'
'2',  '2019-04-10 ',  'Hello 2',  '1',       NULL,     NULL
'3',  '2019-04-11 ',  'Hello 3',  '1',       '1',     'Manish Sijaria'

请建议Mysql中的查询。

Answer 1

您可以执行完全外连接来获取所有值请检查这个answer

所以你的查询应该是

SELECT id, message_date , msg_from, msg_to, sms_text, contact_id, 
       message.user_id, user.fullname  
FROM message left join user on message.user_id=user.user_id 
                               and message.contact_id=1
UNION ALL
 SELECT id, message_date , msg_from, msg_to, sms_text, contact_id, 
       message.user_id, user.fullname  
FROM message right join user on message.user_id=user.user_id 
                               and message.contact_id=1 and message.user_id is null

也请看看这张图片