SQL 如果没有返回值，如何使 select 语句返回 null？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31879407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make a select statement return null if no value is returned?
提问by Crates
Let's say we have the table "letters" like so:
假设我们有这样的表格“字母”：
a | b
-----
0 | 0
0 | 1
1 | 0
1 | 1
And the following select statement:
以及以下选择语句：
SELECT val1, val2, x.a. val3
FROM table1,
table2,
(SELECT a
FROM letters
WHERE a=b
AND a=0) x,
table3
WHERE table1.val1 = table2.val1
AND table1.val2 = table3.val3
I need the embeded SELECT statement (x) to return:
我需要嵌入的 SELECT 语句 (x) 来返回：
0
NULL
NULL
NULL
Instead of what is currently returning which is
而不是当前返回的是
0
Then I want to be able to use that in the big SELECT statement. I hope this makes sense!
然后我希望能够在大的 SELECT 语句中使用它。我希望这是有道理的！
回答by dash
There's a couple of ways you can go about this;
有几种方法可以解决这个问题；
Trivial, using the CASEstatement;
微不足道，使用CASE语句；
SELECT
CASE
WHEN a = b AND a = 0 THEN a
ELSE NULL
END
FROM x
Returns:
返回：
0
NULL
NULL
NULL
Using a LEFT JOIN:
使用左连接：
SELECT X1.a
FROM x
LEFT JOIN (SELECT a, b FROM x WHERE a = b AND a = 0) AS X1 ON x.a = X1.a AND x.b = X1.b
Returns:
返回：
0
NULL
NULL
NULL
回答by Sentinel
The WHEREclause acts as a filter defining which rows are returned, but what you are asking is to have all rows returned, and apply a function on the returned rows to define the values returned for each row.
该WHERE子句充当定义返回哪些行的过滤器，但您要求的是返回所有行，并在返回的行上应用一个函数来定义为每一行返回的值。
SELECT table1.val1
, table1.val2
, x.a
, table3.val3
FROM table1
JOIN table2
on table1.val1 = table2.val1
JOIN table3
on table1.val2 = table3.val3
CROSS JOIN (SELECT CASE WHEN a = b AND a = 0 THEN a END AS a
FROM letters) x
回答by Eric
going off of Dash's comment:
离开 Dash 的评论：
SELECT
CASE
WHEN a = 1 OR b = 1 THEN Null
ELSE 0
END
FROM x
as this would catch your second situation of b = 1, a = 0 and wanting a return of NULL.
因为这会抓住你的第二种情况 b = 1, a = 0 并希望返回 NULL。
回答by aarbor
If you want more than one column returned in the inner select, you'd need to specify those columns in the inner select as well...
如果您希望在内部选择中返回多于一列，则还需要在内部选择中指定这些列...
(SELECT a, NULL 'colname1', NULL 'colname2'
FROM letters
WHERE a=b
AND a=0) x,
You can access those columns using x.colname
您可以使用 x.colname 访问这些列
If the columns could also have values besides NULL, you just need to add the proper joins and return the column values as a normal select statement would.
如果列也可以有除 NULL 之外的值，您只需要添加适当的连接并像普通的 select 语句一样返回列值。
