What is easier to read in EXISTS subqueries?
exists, sql, syntax
Solution
Intuitive is `...EXISTS (SELECT * ..` because you really don't care
- 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.
Problem
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); ``` 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);` The question popped up in comments just now. I researched the manuals of the most popular RDBMS: - 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 also`SELECT 1` in the comments. - SQLite has no example in the language reference. 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 *` 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?