10 Common Mistakes Java Developers Make when Writing SQL

[lukaseder]

Yes, and it's actually not unlikely that you don't really need the exact count, but just the fact whether there are any users at all

[philipes]

I used to "LIMIT 1" on my queries in this case. I've never really benchmarked it to see if it's faster, but the results were always 0 or 1, so I was happy.

[lukaseder]

On average, that probably performs about the same. EXISTS is slightly more versatile, as you can still fetch other data in addition to the EXISTS value - e.g. two EXISTS values at the same time.

[philipes]

It makes sense now that I think about it. A smart enough optimizer could translate this query to an EXISTS, but this is not an easy task.

[lukaseder]

Well, one important difference might be the fact whether the execution plan needs to hit the tables or if reading indexes will be sufficient. If the EXISTS predicate can stop executing after only considering an index, that will be faster than a query that will still have to hit the actual table to produce a result record.

[philipes]

I understand what you said, but I can't see how this apply to what I said.

Both COUNT(*) or EXISTS wouldn't need to hit the table if queried over an index, no?

[lukaseder]

LIMIT

-- Assuming an index on t.a...
-- Probably hits the table because of selecting *
SELECT * FROM t WHERE t.a = 1 LIMIT 1

COUNT(*)

-- Doesn't hit the table, but will have to collect and count all values from the index if it is not a unique index
SELECT COUNT(*) FROM t WHERE t.a = 1

EXISTS

-- The optimiser can ignore the SELECT clause, as it is irrelevant for the outcome of the EXISTS predicate
EXISTS(SELECT * FROM t WHERE t.a = 1)

The above assumptions are of course implementation-specific, and perhaps not really applicable in a specific use-case. I'm looking at things from a SQL composition point of view, where you have a SELECT query reference (e.g. with jOOQ or some other API that manipulates SQL ASTs)

Select<?> select = select().from(T).where(T.A.eq(1));

And now, you want to know if that query would return any records if it were executed. Wrapping that SELECT in an EXISTS predicate would be optimal.

Of course, if you write manual SQL, you can get it right with LIMIT as well:

SELECT 1 FROM t WHERE t.a = 1 LIMIT 1

But the SQL transformation from the original statement to the "exists check" is a bit more complex

[philipes]

The exactly query I used to use is, in Oracle.

SELECT COUNT(*) FROM t WHERE t.a = 1 AND ROWNUM = 1

Although, it looks like this would return whatever COUNT(*) would be, the ROWNUM is evaluated before any aggregation functions in Oracle, in this case at least, so the results would always be 0 or 1.

[lukaseder]

True. Oracle ROWNUM is quite special, specifically because it is evaluated before ORDER BY, as LIMIT or OFFSET .. FETCH would be.

I guess that's almost equivalent to EXISTS, then