SQL查询JOIN两个表，找不到时返回null

Question

table: p
p_id/t_id/
1,35
2,35
3,35
4,35
5,35
6,22
7,22
...

对于表p，p_id的所有可能值都已注册

table: mps
p_id/m_id/value
2,1001,0
4,1001,2
7,1005,1
...

对于表：mps，只注册了几个p_id值

• 我们如何用以下语句连接两个表？

• 输出必须是：p_id、m_id和value

• 输入：m_id(即1001(和t_id(即35(

它必须显示所有p_id组合，当它在表mps中找不到数据时，只显示null。

output example:
p_id, m_id, value
1,1001,null
2,1001,0
3,1001,null
4,1001,2
5,1001,null

我尝试过不同的SQL查询，但当p_id没有在表mps:上注册时，我没有得到输出

SELECT p.p_id, mps.value, mps.m_id 
FROM p LEFT JOIN
     mps
     ON p.p_id = mps.p_id 
WHERE p.t_id = 35 AND mps.m_id=1001

Answer 1

由于不在mps中的任何p_id都将获得NULL mps值，因此需要COALESCE m_id的值，以使其等于不在mps:中的p_id的值的1001

SELECT p.p_id, COALESCE(mps.m_id, 1001) AS m_id, mps.value
FROM p 
LEFT JOIN mps ON p.p_id = mps.p_id AND mps.m_id = 1001
WHERE p.t_id = 35
ORDER BY p.p_id

输出：

p_id    m_id    value
1       1001    (null)
2       1001    0
3       1001    (null)
4       1001    2
5       1001    (null)

SQLFiddle 演示

或者(为了不重复1001值(，您可以将CROSS JOIN m_id = 1001 LEFT JOIN mps:

SELECT p.p_id, m.m_id AS m_id, mps.value
FROM p 
CROSS JOIN (SELECT 1001 AS m_id) m
LEFT JOIN mps ON p.p_id = mps.p_id AND mps.m_id = m.m_id
WHERE p.t_id = 35
ORDER BY p.p_id

输出与上一个查询相同。SQLFiddle 演示

Answer 2

我认为您只想将筛选t_id移到WHERE子句：

SELECT p.p_id, mps.value, mps.m_id, 
FROM p LEFT JOIN
     mps
     ON mps.p_id = p.p_id and mps.m_id = 1001
WHERE p.t_id = 35 

Answer 3

您在mps.m_id中查找的数据有时并不存在，因此您必须自己提供；

SELECT p.p_id
     , 1001 AS m_id
     , mps.value
FROM p
   LEFT JOIN mps ON p.p_id = mps.p_id AND mps.m_id = 1001
WHERE p.p_id = 35;