java - 创建一个选择查询，如果 id 不存在，那么对于该 id，它应该在 SQL 中返回 null

Question

假设我的表 X 具有列 a、b 并且具有如下数据

我需要的查询结果输出是select * from X where b in (111,777,888)

如何实现呢？

标签： javasql-serveroracle

Answer 1

One idea is to build a derived table that holds the values you seach for, and then bring the original table with a left join. In SQL Server, you would use values():

select v.b, t.*
from (values(111), (777), (888)) v(b)
left join mytable t on t.b = v.b
order by v.b

I added a column that holds the value that was intially searched for, since it makes more sense to me - you can remove it if needed.

Oracle does not support this syntax; a (lenghtier) alernative is union all and from dual:

select v.b, t.*
from (
    select 111 b from dual
    union all select 777 from dual
    union all select 888 from dual
) v
left join mytable t on t.b = v.b
order by v.b