带有虚拟行的 SQL in 子句

Question

我真的很想知道是否可以执行一个 select 语句，该语句返回与我们放入 in 子句中完全相同的记录？

示例：

select * from table
where table_id in (1, 2, 3, 666);

此表的示例只有 id-s 从 1 到 100，因此此选择将仅返回三行。我需要做什么才能获得 666 的一行（可能为空或虚拟）？

谢谢！

Answer 1

您可以使用 union:

select * from table
where table_id in (1, 2, 3);
union
select 666 as table_id, other_fields_with_dummy_values_in_table from dual;

在 Oracle 中可以这样做。 from Dual 可能会有所不同，具体取决于您使用的数据库系统。

请注意，如果您使用联合，您的虚拟查询必须选择与真实查询相同的记录。

Answer 2

您可以在没有表的情况下进行选择

只需对查询进行 UNION

select table_id, some_column from table
where table_id in (1, 2, 3, 666);
union
select 666, 'dummy_data'

Answer 3

假设一个表 numbers 包含从 1 到 1000000 的所有数字（实际上足以覆盖您的输入值范围），您可以运行以下 SQL：

SELECT *
  FROM numbers left outer join table on table.table_id = numbers.number
 WHERE numbers.number in (1, 2, 3, 666)

如果您使用的 DBMS 提供更好的解决方案，例如 SQL Anywhere 的 sa_rowgenerator 过程，可以用过程调用替换表numbers，并且没有最大数量的限制。

Answer 4

IN 子句是一个布尔谓词，因此您需要将其替换为虚拟记录集：

SELECT  m.*
FROM    (
        SELECT  1 AS id
        UNION ALL
        SELECT  2 AS id
        UNION ALL
        SELECT  3 AS id
        UNION ALL
        SELECT  666 AS id
        ) q
LEFT JOIN
        mytable m
ON      m.id = q.id

在 SQL Server 2008 中，您可以运行此查询：

SELECT  *
FROM    @mydata d
LEFT JOIN
        mytable t
ON      t.id = d.id

使用 @ mydate 是一个表变量，作为参数从客户端传递。

在 PostgreSQL 中，您可以运行以下查询：

SELECT  *
FROM    (
        SELECT  :arr[s] AS id
        FROM    generate_series(1, array_upper(:arr, 1)) s
        ) q
LEFT JOIN
        mytable t
ON      t.id = q.id

其中 :arr 是一个数组 [1, 2, 3, 666]，也是从客户端作为参数。

在 Oracle 中，您可以执行以下操作：

SELECT  *
FROM    TABLE(:mycol) q
LEFT JOIN
        mytable t
ON      t.id = q.id

，其中 :mycol 是从客户端传递的集合类型的变量。

Answer 5

一种思考方式是：您需要让该数据作为数据集“输入”查询。在 where 子句中找到的数据永远不会“添加”到查询中，它们仅用于过滤掉现有数据。

一个简单的例子：

DECLARE @MustInclude (Value  int  not null)

INSERT @MustInclude (Value) values (1)
INSERT @MustInclude (Value) values (2)
INSERT @MustInclude (Value) values (3)
INSERT @MustInclude (Value) values (666)

SELECT *
 from @MustInclude mi
  left outer join MyTable mt
   on mt.Value = mi.Value