Below you will find pages that utilize the taxonomy term “Technical”
Count and Its Many Guises
Context
So why am I blogging about the count command today? Its mainly because I keep on seeing this come up on my feed. Normally the posts are under the guise of look at this interview question or junior DBAs should know this.
Which is great but the articles rarely show why one is better then the other, or even how the query planner looks at the query that you are running. The conclusions of this blog may be the same as the quick articles but hopefully this will provide a bit more information as to the WHY. This post relates to the POSTGRES dbengine.
SQL - Execution Order
So firstly understanding
So the point of this post is to understand how the SQL engine (and this is platform agnostic) looks and understands your query.
Whilst the improvements in DB engines have been amazing over the years this topic is fundamental understanding to how the DB engine will interpret your query commands - it can mean the difference between a really happy DBA or one that is reaching for the access remove script.
EXIST or IN or ANY
Use of IN and EXISTS and ANY
So, there are various different ways to compare you datasets against a list of values, you can use IN, EXISTS or ANY keywords. The only problem is which to use and when. Please note that the contents of this post is based on my experiences - you may find this works differently with your datasets.
Please note that this post applies to Postgresql only, given that different engines treat these commands in different ways.