SQL:如何返回不存在的行? [英] SQL: How to return an non-existing row?

问题描述

说我有下表:

=================================================
| color_id | parent_id | language_id | name     |
=================================================
| 1        | 50        | 1           | Black    |
-------------------------------------------------

然后说我需要行WHERE parent_id = 50 AND language_id = 2.显然，基于示例表，我什么也得不到.但是，我仍然需要一个结果-大概是这样的:

Then say I need the row WHERE parent_id = 50 AND language_id = 2. Obviously, I would get nothing back based on my example table. However, I still need a result -- probably something like this:

=================================================
| color_id | parent_id | language_id | name     |
=================================================
| NULL     | 50        | 2           | NULL     |
-------------------------------------------------

是否可以在SQL中执行此操作?

Is there a way to do this in SQL?

推荐答案

您可以对可能有效的记录和默认记录进行联合查询，然后选择第一个:

You could do a union query of both the potentially valid record and your default, then select the first one:

SELECT * FROM
(SELECT color_id, parent_id, language_id, name, 1 as order_rank
 FROM some_table
 WHERE parent_id = %parent_id% AND language_id = %language_id%
 UNION
 SELECT NULL, %parent_id%, %language_id%, NULL, 2 as order_rank
)
ORDER BY order_rank
LIMIT 1

(根据OMG Ponies的建议，使用静态值进行编辑以进行订购)

(Edited with static value for ordering as suggested by OMG Ponies)

这篇关于SQL:如何返回不存在的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！