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

刚才在评论中弹出了这个问题.我研究了最流行的 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 在SELECT *rel="noreferrer">语言参考.
• MySQL 5.7 在 参考手册 以及注释中的SELECT 1.
• 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屋！