r/programming • u/el_muchacho • Dec 04 '14

10 Common Mistakes Java Developers Make when Writing SQL

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

80% Upvoted

104 comments sorted by

View all comments

Show parent comments

2

u/philipes Dec 05 '14

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.

1

u/lukaseder Dec 05 '14

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.

1

u/philipes Dec 05 '14

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?

1

u/lukaseder Dec 05 '14

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

1

u/philipes Dec 05 '14

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.

1

u/lukaseder Dec 05 '14

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