What is easier to read in EXISTS subqueries? [closed]

Question

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 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?

Nies

Why would SELECT 1 be like saying "if at least one exists"? I don't see that makes intuitive sense at all. If someone wrote SELECT 2 would you intuitively think that was checking at least 2 exist? – Dacey

@MartinSmith: you are using intuition and thinking in one sentence there. – Nies

And the problem with that is what? Intuition is a thought process. – Dacey

@MartinSmith: I'd more closely relate intuition to perception, while I'd describe thinking as an active rational process. But that gets philosophical quickly. – Nies

But intuition leads to a thought or there is no point surely? – Dacey

Maybe these might be better suited to discuss these non-development concepts? linguistics.stackexchange.com and philosophy.stackexchange.com – Eckhardt

@Eckhardt - Thanks, but that's me done on the issue! – Dacey

@Erwin, I know you have already said you won't consider select NULL, but I would ask you to reconsider. The only time anyone would select null is where they don't care what is being returned - to me, it signifies that the only purpose of the query is to check for existence, and is therefore more intuitive than any other option. – Dumbwaiter

@MarkBannister: The only time I would select NULL? That's just not true. Example (one of many): INSERT INTO foo SELECT NULL FROM bar WHERE baz; – Nies

@Erwin: why would you ever insert multiple null rows into a single-columned table? – Dumbwaiter

@MarkBannister: For instance when foo has meaningful defaults and only the time of execution and the number of rows is relevant. – Nies

@Erwin: unless you were using auto-generated unique IDs, that would give you duplicate rows. Inserting a single count value would seem to make more sense. – Dumbwaiter

@MarkBannister: No, it would not make more sense. The point is, there are valid uses of SELECT NULL. – Nies

@Erwin: no, this insert would violate first normal form. There may be other valid uses of SELECT NULL, but you haven't suggested any. – Dumbwaiter

let us continue this discussion in chat – Dumbwaiter

@MarkBannister: not sure if you have seen my answer in the chat. It's gone by now. You are right about violating 1st NF. My example was sloppy. The example would have to be INSERT INTO foo(val) SELECT NULL FROM bar WHERE baz; for a table defined TABLE foo(id serial, val text, ts timestamp default current_timestamp). In other words: insert a number of events with an unknown value, but the order of events and / or the timestamp are relevant. – Nies

Voted to re-open. @Erwin, I can see why you might want to insert one value at a time, to record timestamps, with a select null. I think this is another example of what I said in my original comment - "The only time anyone would select null is where they don't care what is being returned". Inside a sub-query, the only time you would do this (that I can think of) would be in an exists clause, which is why I think this is the most intuitive. – Dumbwaiter

You want to know which is more intuitive, but the only place intuitiveness would add value here would be for real beginners. I have had beginners ask me before why I was doing "Select 1", but they seem to understand what's going on pretty intuitively with "Select *". So the fact that the one raises the question and the other doesn't leads me to believe * is more intuitive. – Wristwatch

Answer 1

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.

Eckhardt

What general myths? SELECT 1 has my vote – Kristianson

@EvanCarroll why? – Eckhardt

(a) the spec defines it as the same, (b) it's cleaner -- you clearly don't care about the columns, (c) the behavior is more consistent on different versions, some databases don't optimize the condition away yet. – Kristianson

Well, if you care for absolute "cleanness", EXISTS (SELECT FROM a) works fine in Postgres ;) – Theorize

I always use EXISTS ( SELECT 'any rows?' FROM... because it's in the spirit of the language to read like English. – Footton

@Footton That's quite a good idea and is completely ignored by the engine. – Eckhardt

I thought so :) and it's not one of the "fun" (silly) ones like select 1/0 – Footton

@ErwinBrandstetter the question has been closed (since 2011 !!) – Theorize

@ypercubeᵀᴹ: Oh, right. :) I just linked to this one from a new question. And your comment is the answer I would finally accept. Sorry for the noise. (Shouldn't have been closed IMHO, btw.) – Nies

Answer 2

I still use EXISTS (SELECT * ...), for historical (gbn: should that be hysterical?) reasons. Technically, there is no difference, of course; the optimiser / planner will throw it away and reduce it to one bit of information. For the human reader the * looks more special, it will stand out as a special symbol, and not as a value or constant. Also, I tend to reduce the amount of literals and magic constants in my programs (eventually, only 0 and 1 should remain).

Steeve

Do you mean "hysterical" or "historical"...? "hysterical" is more correct when talking about the myths and superstitions... – Eckhardt

The pun was intended... – Steeve

Answer 3

In the context of EXISTS the SQL optimizer knows that it doesn't matter what it returns as long as it returns something. So to you it doesn't matter.

For the intuitive part: I don't think * will be right.

It's better to ask in words: "check whether even the slightest part exists" - meaning 1 (or something else).

Reproduce

The slightest part is a row here. That's what EXISTS checks, if a row exists. That's why SELECT * as SELECT the-whole-row looks more intuitive to many. – Theorize

Thanks. for correcting. I'm still having difficulties understanding @gbn's answer. if the inside loop does select * so this is being translated behind the scenes to columns - which takes more time. my solution returns only 1. I dont care about columns. Am I wrong ? ( lol and remove this 93 age of yours hhhhhh:-) – Reproduce

Yes, even MySQL (not so clever) optimizer knows that EXISTS (SELECT 1 ...) and EXISTS (SELECT * ...) will either be true or false, depending on whether there is a row that would be returned by the subquery. In other words, optimizers know that they don't have to actually run the subquery and return something. They only check if it would return a row. Think of it as "the optimizers runs it always as (SELECT 1 ...)" when it is inside an EXISTS. – Theorize

@ypercube how can this be checked ? I mean something like refelector for sql...? the execution plan wont show me this conversion from select * or select 1 ....this is the kind of things I dont like about SQL. – Reproduce

This is different for each product. For MySQL for example, check this: Tracing the Optimizer – Theorize

PostgreSQL cares. That said, SELECT 1 has my vote. – Kristianson

In fact, even select null would work in PostgreSQL, It is the existence of rows what exists measures – Shire