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: SELECT count(*) INTO v_any_wahlbergs FROM actor a JOIN film_actor fa USING (actor_id) WHERE a.last_name = 'WAHLBERG' ... … Continue reading Don’t Even use COUNT(*) For Primary Key Existence Checks
Tag: exists
Avoid Using COUNT() in SQL When You Could Use EXISTS()
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 COUNT() in SQL When You Could Use EXISTS()
SQL JOIN or EXISTS? Chances Are, You’re Doing it Wrong
I've noticed this very consistently with a lot of customers, and also with participants of our Data Geekery SQL Workshop (which I highly recommend to everyone, if you excuse the advertising): 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 … Continue reading SQL JOIN or EXISTS? Chances Are, You’re Doing it Wrong
SQL Tip of the Day: Be Wary of SELECT COUNT(*)
Recently, I've encountered this sort of query all over the place at a customer site: DECLARE v_var NUMBER(10); BEGIN SELECT COUNT(*) INTO v_var FROM table1 JOIN table2 ON table1.t1_id = table2.t1_id JOIN table3 ON table2.t2_id = table3.t2_id ... WHERE some_predicate; IF (v_var = 0) THEN do_something ELSE do_something_else END IF; END; Unfortunately, COUNT(*) is often … Continue reading SQL Tip of the Day: Be Wary of SELECT COUNT(*)
The truth about IN and EXISTS in SQL
Very nice article, finally getting rid of some doubts... (at least for Oracle) http://explainextended.com/2009/09/30/in-vs-join-vs-exists-oracle/