在EXISTS子查询中，什么更容易阅读? [英] What is easier to read in EXISTS subqueries?

问题描述

这是可读性的问题.性能没有差异.
_{SQL Server的旧版本足够愚蠢，无法查找元数据.}

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或有趣的变体"，这对我来说似乎并不直观.

I am not considering NULL or "fun variants" which don't seem intuitive to me.

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);

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的手册:

The question popped up in comments just now. I researched the manuals of the most popular RDBMS:

• MS SQL在手册中似乎更倾向于SELECT * .
• PostgreSQL 9.4手册中的示例使用SELECT 1.
• Oracle 11g在语言参考.
• MySQL 5.7在语言参考中没有SQLite示例.

• MS SQL seems to favor SELECT * in the manual.
• The example in the PostgreSQL 9.4 manual uses SELECT 1.
• Oracle 11g has SELECT * in the language reference.
• MySQL 5.7 has SELECT * in the reference manual but alsoSELECT 1 in the comments.
• SQLite has no example in the language reference.

在SO上搜索code:"EXISTS (SELECT 1"会得到5,048个结果.
在SO上搜索code:"EXISTS (SELECT *"会产生5,154个结果.
_{更新的链接和计数07.2015.}

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.}

因此，SELECT *具有广泛的选票，并拥有大型的商业RDBMS.
我发现SELECT 1更直观.就像说如果至少存在一个".
SELECT *更直观吗?

So SELECT * has the popular vote and the big commercial RDBMS on its side.
I find SELECT 1 more intuitive. It's like saying "if at least one exists".
Is SELECT * more intuitive?

推荐答案

直觉是...EXISTS (SELECT * ..，因为您真的不在乎

• 唯一重要的关键字是 EXISTS
• 选择...EXISTS (SELECT 1 ..可以使普遍存在的关于EXISTS的神话和迷信(例如，对MySQL文档的评论).
• ANSI标准说没关系"
• 了解EXISTS是半联接是更加有趣的事情.

• The only keyword of importance is EXISTS
• The choice of ...EXISTS (SELECT 1 .. perpetuates the general myths and superstitions around EXISTS (eg comments on the MySQL docs).
• ANSI standard says "doesn't matter"
• It's more interesting to understand that EXISTS is a semi-join.

这篇关于在EXISTS子查询中，什么更容易阅读?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！