Common Mistakes and Missed Optimization Opportunities in SQL

While counting columns will not include NULL columns, how about counting joined tables?<p><pre><code> SELECT a.id, COUNT(b.*) FROM a JOIN b ON b.a_id = a.id GROUP BY a.id </code></pre> is not permitted in Postgres.<p>Sure, I could just use COUNT(b.a_id) since that&#x27;s what I join on, but a more complicated example might not allow for that. For instance if it was a virtual table.

Some of these have been learned through trial and error over the years, but a few were new and great to know.<p>On a related note, is the MCSE the gold standard for SQL education? Have been looking for a way to brush up and formalize my SQL skills.

Would someone please confirm whether this article is misrepresenting a subquery as an inline CTE? It is my understanding that as of Postgresql 12, a programmer denotes a CTE as &quot;AS MATERIALIZED&quot;, &quot;AS NOT MATERIALIZED&quot;, or neither and allow the default operation to happen: the CTE subquery will default to inline if its result is used once.<p>for reference: <a href="https:&#x2F;&#x2F;sudonull.com&#x2F;posts&#x2F;998-Important-changes-in-the-CTE-in-PostgreSQL-12" rel="nofollow">https:&#x2F;&#x2F;sudonull.com&#x2F;posts&#x2F;998-Important-changes-in-the-CTE-...</a><p>Generally speaking, some clarification would be helpful!

This is a pretty trivial list. Useful for beginners I guess.<p>I seriously take issue with &quot;Reference Column Position in GROUP BY and ORDER BY&quot; though. <i>If</i> it is restricted to ad-hoc (AKA messing-about) queries I&#x27;d be fine with it, but it won&#x27;t be. Just don&#x27;t do it.

I&#x27;d never run across coalesce before. I usually end up doing nested NVL calls if I&#x27;m trying to find the first non-null in a series of expressions (I&#x27;m on Oracle, btw). I&#x27;ve now added this function to my toolbox.

I would add to the common mistakes (should be generic, but I have more xp with sql server) :<p>not indexing, most often, tables are not or poorly indexed.<p>Implicit conversion can generate a lot of io&#x2F;leads to poor perf or just not using indexes.<p>Sql function:sorry but they are most often crap and useless, better to in-line or use TVF, and no its not code logic duplication.<p>Read uncommitted unless you enjoy not reading rows, multiple times or half of a value (page split and&#x2F;or LOB values)

In the &quot;Avoid Transformations on Indexed Fields&quot; part, I fail to understand how the example can work if you&#x27;re applying the timezone computation on the right-hand side.<p>I&#x27;m not familiar with MS SQL (I&#x27;ve only worked with MySQL &#x2F; PostgreSQL), can someone explain me how it works?

In regards to formatting sql, I used to do it the way shown, but a coworker formatted the columns in the select with the commas in front. This seemed strange to me until I tried it. I realized that this solved the problem of sometimes a query would be changed and the last item in the select list would be removed, but the last comma would not be removed. Or, a new item was added to the end of the select list, but they neglected to add in a comma at the end of the previous last item.<p>SELECT<p><pre><code> col1 ,col2 ,COUNT(col3) </code></pre> FROM<p><pre><code> t1 JOIN t2 ON ta.pk = t2.fk </code></pre> WHERE<p><pre><code> col1 = col2 AND col3 &gt; col4 </code></pre> GROUP BY<p><pre><code> col1 ,col2 </code></pre> HAVING<p><pre><code> COUNT(col3) &gt; 1</code></pre>

I wish I could use ON for the selection criteria for the first table instead of a where clause:<p>Select A.value, B.valuue<p>from tableA A on A.id = 77<p>join tableB B on B.id = A.bId

&gt; 2019-22-11: Fixed the examples in the &quot;Faux Predicate&quot; section after several keen eyed readers noticed it was backwards.<p>What abomination of a date format is this? I can only assume this is a bug, a typo, or an easter egg for those paying attention. <i>Please</i> let it be one of those. The last thing the world needs is people pushing yet another crazy date format into use.

Edit: “Don’t use an ORM” should be point 1