It's a question of readability. There is no difference in performance. Old versions of SQL Server were silly enough to look up meta data, but not any more.
SELECT foo FROM bar WHERE EXISTS (SELECT * FROM baz WHERE baz.id = bar.id);
SELECT foo FROM bar WHERE EXISTS (SELECT 1 FROM baz WHERE baz.id = bar.id);
我没有考虑 NULL 或“有趣的变体”,这对我来说似乎并不直观。
SELECT foo FROM bar WHERE EXISTS (SELECT NULL FROM baz WHERE baz.id = bar.id);
SELECT foo FROM bar WHERE EXISTS (SELECT 1/0 FROM baz WHERE baz.id = bar.id);罢工>
刚才评论里出现了这个问题。我研究了最流行的 RDBMS 的手册:
MS SQL 似乎更青睐SELECT *在手册中.
The PostgreSQL 9.4 手册中的示例 uses SELECT 1.
Oracle 11g 有SELECT * in the 语言参考.
MySQL 5.7 有SELECT * in the 参考手册但是也SELECT 1在评论中。
SQLite 中没有示例语言参考.
A search on SO for code:"EXISTS (SELECT 1" yields 5,048 results.
A search on SO for code:"EXISTS (SELECT *" yields 5,154 results. Updated links and counts 07.2015.
So SELECT *拥有大众投票和大型商业 RDBMS 的支持。
I find SELECT 1更直观。这就像说“如果至少有一个存在”。
Is SELECT *更直观?
直观的是...EXISTS (SELECT * ..因为你真的不在乎
唯一重要的关键字是EXISTS
的选择...EXISTS (SELECT 1 ..延续了围绕 EXISTS 的普遍神话和迷信(例如,对 MySQL 文档的评论)。