mysql dual使用

在编写sql语句时，dual表可以作为一个空表在任意场合使用。
例：

select 7 * 9 from dual;   # 计算器
+-----+
| 7*9 |
+-----+
|  63 |
+-----+

这样做是为了保持sql语句的书写习惯。以下写法也可以实现相同效果：

select 7 * 9 
+-----+
| 7*9 |
+-----+
|  63 |
+-----+

但如果要附加条件，那就必须要带上dual

# success
select 7 * 9 from dual where exists(selec 1 from student where id=10);
+-----+
| 7*9 |
+-----+
|  63 |
+-----+

# failed
select 7 * 9 where exists(selec 1 from student where id=10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where exists (select 1 from student where id=10)' at line 1

mysql文档中对于dual表的解释：

You are allowed to specify DUAL as a dummy table name in situations
where no tables are referenced

你可以在没有表的情况下指定一个虚拟的表名 mysql> SELECT 1 + 1 FROM DUAL;

DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.

DUAL是为了方便那些要求所有SELECT语句都应该具有FROM和其他子句的人。MySQL可能会忽略该条款。如果没有引用表，MySQL不需要从DUAL。