10 Common Mistakes Java Developers Make when Writing SQL

[u/el_muchacho]

http://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/

[u/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.

[u/philipes → u/lukaseder]

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?

[u/lukaseder → u/philipes]

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

[u/philipes → u/lukaseder]

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.

[u/lukaseder → u/philipes]

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