子查询作为嵌套SQL列表的元素

Question

TLDR

是否有一种SQL方言不仅(1)允许嵌套列表，而且(2)允许SQL子查询成为这样一个列表的有效元素？

SELECT *
FROM tuples
WHERE (dim_1, dim_2) IN ((1, 2), (SELECT DISTINCT x, y FROM coords LIMIT 2))

背景

很抱歉，由于我的领域知识狭窄，而不是因为代码错误，所以发布了一个学术问题。我在这里发布这个问题是因为我一直无法在网上找到答案:大多数搜索结果处理嵌套子查询，而其他任何搜索结果都处理包含标量元素的嵌套列表。

我正在编写一个R函数insert_params()，以便从任意的R list()动态创建一个SQL列表(通过DBI::sqlInterpolate()防止注入)，对于嵌套列表使用可选的递归(或者通过unlist()进行扁平化)。我想到了这样的情况

SELECT *
FROM my_table
WHERE (field_1, field_2) IN (('bobby', 'tables'), ('strawberry', 'fields'), (SELECT 0 WHERE 0 = 1))

其中，insert_params()被设计为(可选)插入一个空子查询，作为来自R的空list()的语法上有效的表示形式。

虽然我目前正在使用Microsoft (它似乎不允许嵌套SQL列表)，但我不希望insert_params()限制用户使用其他SQL方言，因此我希望增强处理嵌套列表的灵活性。

现在，insert_params()确实生成了像上面那样的string，就像预期的那样。但是，我想确保这个字符串在那些允许嵌套列表的SQL方言中在语法上是有效的；否则，这个功能就没有意义了。

谢谢你的帮忙!

编辑：

我还想知道这类方言(如果存在)和查询是否会对ANSI的遵从性提出问题。我对ANSI遵从性不太了解，但是insert_params()确实与DBI::sqlInterpolate()一起使用DBI::ANSI对象。DBI::ANSI的每个R文档

描述 模拟ANSI-SQL遵从性的虚拟DBI连接器

Answer 1

不是的。你可以用两种方法之一来处理这件事。我建议：

WHERE (dim_1, dim_2) IN ((1, 2)) OR
      (dim_1, dim_2) IN (SELECT x, y FROM coords LIMIT 2))

或者：

WHERE (dim_1, dim_2) IN ( (SELECT x, y FROM coords LIMIT 2) 
                          UNION ALL
                          (SELECT 1, 2)
                        )

备注：

• SELECT DISTINCT不需要与IN一起使用。
• LIMIT不是标准的SQL。一般的SQL将是FETCH FIRST 2 ROWS ONLY。
• 并不是所有的数据库都支持没有SELECT的FROM，因此第二个解决方案需要在这些数据库中进行调整。

Answer 2

Oracle具有这样的特性，例如，这是一个有效的Oracle SQL查询

SELECT * 
FROM mytable
WHERE (a, b) IN (((select max(a) from mytable), (select max(b) from mytable)), (9,5));

注意元组的IN列表中的子查询。

db<>fiddle

这也是有效的查询。

SELECT * 
FROM mytable
WHERE (a, b) IN (
    select a+1, b+1 from mytable union all
    select 9,5 from dual union all
    select (select max(a) from mytable), (select max(b) from mytable) from dual)