In a previous blog post, we’ve advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of: Do this: (Depending on your dialect you may require a FROM DUAL clause, … Continue reading An Efficient…
#exists
5 posts
16 Feb 2024
28 Oct 2016
In a recent blog post, I’ve advocated against the use of COUNT(*) in SQL, when a simple EXISTS() would suffice. This is important stuff. I keep tuning productive queries where a customer runs a COUNT(*) query like so: … where after they discard the exact count to only check for existence: It doesn’t matter if … Continue reading Don’t Even…
14 Sept 2016
A while ago, I blogged about the importance of avoiding unnecessary COUNT(*) queries:https://blog.jooq.org/sql-tip-of-the-day-be-wary-of-select-count … and how to replace them with equivalent EXISTS queries As I’m updating the SQL training to show also PostgreSQL performance characteristics in addition to Oracle, I really have to reiterate this topic. Please repeat after me: Thou shalt not use COUNT(*) … Continue reading Avoid Using…
9 Mar 2016
A lot of developers get the distinction between JOIN and SEMI-JOIN wrong. Let me explain… What are JOIN and SEMI-JOIN A little bit of relational algebra first. What is an (INNER) JOIN? An JOIN is nothing but a filtered cartesian product. And what is a cartesian product? Wikipedia explains this very nicely: for sets A … Continue reading SQL JOIN…
8 Aug 2014
Recently, I’ve encountered this sort of query all over the place at a customer site: Unfortunately, COUNT(*) is often the first solution that comes to mind when we want to check our relations for some predicate. But COUNT() is expensive, especially if all we’re doing is checking our relations for existence. Does the word ring … Continue reading SQL Tip…