Menu
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:
SELECT * in the manual.SELECT 1.SELECT * in the language reference.SELECT * in the reference manual but alsoSELECT 1 in the comments.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?
243
Use EXISTS to identify the existence of a relationship without regard for the quantity. For example, EXISTS returns true if the subquery returns any rows, and [NOT] EXISTS returns true if the subquery returns no rows. The EXISTS condition is considered to be met if the subquery returns at least one row.
A subquery that is introduced with exists is different from other subqueries, in these ways: The keyword exists is not preceded by a column name, constant, or other expression. The subquery exists evaluates to TRUE or FALSE rather than returning any data.
In MySQL 8.0. 19 and later, you can also use NOT EXISTS or NOT EXISTS with TABLE in the subquery, like this: SELECT column1 FROM t1 WHERE EXISTS (TABLE t2); The results are the same as when using SELECT * with no WHERE clause in the subquery.
PostgreSQL EXISTS examples The NOT EXISTS is opposite to EXISTS . It means that if the subquery returns no row, the NOT EXISTS returns true. If the subquery returns one or more rows, the NOT EXISTS returns false. The following example returns customers have not made any payment that greater than 11.
Intuitive is ...EXISTS (SELECT * .. because you really don't care
...EXISTS (SELECT 1 .. perpetuates the general myths and superstitions around EXISTS (eg comments on the MySQL docs).
53
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
