I need to write up some notes, but there are some links that will help me:
- [WayBack] progress db – Exclude rows based on other rows (SQL) – Stack Overflow
- [WayBack] sql – EXISTS vs JOIN and use of EXISTS clause – Stack Overflow
- [WayBack] sql – is there something faster than “having count” for large tables? – Stack Overflow
- [WayBack] sql server – EXISTS (SELECT 1 …) vs EXISTS (SELECT * …) One or the other? – Database Administrators Stack Exchange has even more elaborate answers than [WayBack] sql – What is easier to read in EXISTS subqueries? – Stack Overflow
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 alsoSELECT 1in 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 forcode:"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 findSELECT 1more intuitive. It’s like saying “if at least one exists”.
IsSELECT *more intuitive?
–jeroen
The Wiert Corner - irregular stream of stuff 