MYSQL:不能/如何将子查询用作完全合格的 CTE

Question

我想执行以下操作。这在 MySQL 中是非法的。 PostGRESQL 中关联的 CTE(“with”子句)有效。这里的假设是 MySQL 中的子查询不是完全限定的 CTE。

请注意:这个查询显然非常愚蠢，为了您的观看乐趣并简洁地突出问题，它刚刚被清理和缩短。

另请注意，“获得对称差异”任务与手头的问题正交。

SELECT A.val
FROM
  (SELECT val FROM tableA) AS A,
  (SELECT val FROM tableB) AS B
WHERE (A.val NOT IN (SELECT val FROM B)
       OR B.val NOT IN (SELECT val FROM A));

PostGRES 示例(坦率地说，没有测试过，因为我目前没有要测试的 PostGRES 数据库，但我 100% 确定它会工作...我过去做过类似的事情) :

WITH A as (SELECT val FROM tableA),
     B as (SELECT val from tableB)
SELECT A.val FROM A, B
WHERE (A.val NOT IN (SELECT val FROM B)
       OR B.val NOT IN (SELECT val FROM A));

Answer 1

更新

MySQL 8.0 引入了对公用表表达式 (CTE) 的支持，即早期版本的 MySQL 不支持的“WITH”。

https://dev.mysql.com/doc/refman/8.0/en/with.html

---

编辑

是的，我们可以为内联 View (或 MySQL 术语中的派生表)分配一个别名，并在查询的其他地方引用该别名。在此示例中，my_query 是我们分配给内联 View 的别名。

 SELECT my_query.val 
   FROM ( SELECT val FROM foo )  my_query
  WHERE my_query.val 
  ORDER
     BY my_query.val 

但是我们不能在子查询的 FROM 子句中引用该别名。例如，在这个结构中:

 SELECT my_query.val 
   FROM ( SELECT val FROM foo )  my_query
  WHERE 7 IN ( SELECT val FROM my_query )
  ORDER
     BY my_query.val

语法 实际上是有效的。但是 IN(子查询) 的 FROM 子句中对 my_query 的引用不是也不可能是对表别名的引用外部查询。这是不允许的。

所以我们希望查询可能会抛出一个错误，关于子查询中的 my_query 引用是未解析的标识符“未知表”，诸如此类。

请注意，我们可以创建一个名为my_query 的表，例如

CREATE TABLE my_query (val INT PRIMARY KEY);
INSERT INTO my_query (val) VALUES (7);

然后重新运行查询，该引用 my_query 现在将解析为表。 (不是查询中指定的别名。)

公用表表达式 (CTE) 在解决此类问题方面向前迈出了重要一步，能够在不允许的多个位置引用内联 View 。

如果问题是“How to emulate Common Table Expression (CTE) functionality in MySQL”，谷歌搜索会导致这个问题:

How do you use the "WITH" clause in MySQL?

原创

---

您是指 CTE(公用表表达式，即 WITH 子句)而不是 CMT 吗？

如果您指的是 CMT，我深表歉意，因为在这个问题的上下文中，这个首字母缩略词代表什么还不清楚。也许这意味着集中管理表，但我不明白这有什么关系。

如果您问 MySQL 是否支持公用表表达式 (CTE)，答案是否定的。或者，至少，现在还没有。

---

从发布的 SQL 示例来看，似乎不需要 CTE 或内联 View ，甚至不需要子查询。也许 SQL 已经过分简化到不清楚查询应该实现什么的程度。 (它不应该得到对称差异，所以我们不会建议明显的替代方案，如何在 MySQL 中做到这一点。

在 IN 和 NOT IN 中使用子查询(SELECT)是有效的。这些都是有效的:

    foo IN ( subquery )

    foo NOT IN ( subquery )

NOT IN (subquery) 的一个大问题是子查询返回 NULL 值。对于列表中的 NULL 值，NOT IN 的计算结果为 NULL，这意味着它不能对任何行计算为 TRUE。作为演示，请考虑:

 SELECT 3 NOT IN (2,NULL,4)

也许这可以回答所提出的问题。

我们只是在猜测问的是什么问题。 (有没有提问？)

“不起作用”是什么意思？查询返回意外结果？ MySQL 返回错误？查询执行了很长时间并出现“挂起”？我们只是在猜测 DOES NOT WORK 是什么意思。

---

为什么我们对连接操作使用老式的逗号运算符语法，而不是(我们还能调用它吗？更新的)JOIN 关键字？