如果子查询（内部查询）将零值返回到外部查询，如何处理如何处理

Question

我有一个类似的垂直

id	profile_id	feature_id	value
1	1	1	rick
2	1	2	Novak
3	5	3	5428
4	5	1	Joe
...	...	...	：

表

我有一个查询：

SELECT * FROM(SELECT `value` TelNum FROM `profile_features` WHERE `feature_id` IN (10, 64, 103) AND `profile_id` = 16752 LIMIT 1) as TelNum,
    (SELECT `value` NCode From `profile_features` WHERE `feature_id` IN (5, 61, 100) AND `profile_id` = 16752 LIMIT 1)  AS NCode,
    (SELECT `value` Fname From `profile_features` WHERE `feature_id` IN (1, 55, 86) AND `profile_id` = 16752 LIMIT 1) AS Fname,
    (SELECT `value` Lname From `profile_features` WHERE `feature_id` IN (2, 56, 95) AND `profile_id` = 16752 LIMIT 1) AS Lname

但是，如果其中一个子查询返回null，则查询没有任何输出。

我想要Telnum，ncode，fname，lname的行结果，它们要么为null或不为空。

Answer 1

您可以在当前的笛卡尔产品中使用Select List中的标量子查询：

SELECT (SELECT `value` FROM `profile_features` WHERE `feature_id` IN (10, 64, 103) AND `profile_id` = 16752 LIMIT 1) as TelNum,
  (SELECT `value` From `profile_features` WHERE `feature_id` IN (5, 61, 100) AND `profile_id` = 16752 LIMIT 1)  AS NCode,
  (SELECT `value` From `profile_features` WHERE `feature_id` IN (1, 55, 86) AND `profile_id` = 16752 LIMIT 1) AS Fname,
  (SELECT `value` From `profile_features` WHERE `feature_id` IN (2, 56, 95) AND `profile_id` = 16752 LIMIT 1) AS Lname;

另外，您可以学习使用左外的Join：

SELECT TelNum.TelNum, NCode.NCode, Fname.Fname, Lname.Lname
FROM (SELECT NULL AS dummy) AS dummy
LEFT OUTER JOIN (SELECT `value` TelNum FROM `profile_features` WHERE `feature_id` IN (10, 64, 103) AND `profile_id` = 16752 LIMIT 1) AS TelNum ON true
LEFT OUTER JOIN (SELECT `value` NCode From `profile_features` WHERE `feature_id` IN (5, 61, 100) AND `profile_id` = 16752 LIMIT 1) AS NCode ON true
LEFT OUTER JOIN (SELECT `value` Fname From `profile_features` WHERE `feature_id` IN (1, 55, 86) AND `profile_id` = 16752 LIMIT 1) AS Fname ON true
LEFT OUTER JOIN (SELECT `value` Lname From `profile_features` WHERE `feature_id` IN (2, 56, 95) AND `profile_id` = 16752 LIMIT 1) AS Lname ON true;