Writing better SQL in context of a programmer

Often developers / programmers mix object-oriented thinking with imperative thinking depending on their levels of skill, dogma and mood.
But when programmers write SQL, everything changes. SQL is a declarative language that is closer to natural language and has nothing to do with either object-oriented or imperative thinking. It is very easy to express something as a SQL query like we do in natural language. But it is not so easy to express it optimally or correctly with SQL.
Not only developers need to re-think their programming paradigm when writing SQL, they also need to think it in terms of mathematical set theory.
Writing Better SQL
Followings are the common mistakes that a developer makes when writing SQL queries in their programs.

1. NULL checking:
A novice developer’s first mistake is misunderstanding the NULL when they write SQL. Most of the time this misunderstanding is thinking that NULL is equals to NULL, which is not. This misunderstanding often lead to data discrepancy when joining tables.

2. Processing data using programming language
Only few good developers know SQL very well. Most of developers’ use JOIN, the odd UNION but not everyone uses window functions, grouping sets. A lot of programmers load SQL data into memory then transform the data into some appropriate collection type and do the nasty arithmetic operations on that collection using loops or verbose methods. But when one use SQL to do math it is simpler and it is faster or perform better.

3. Using WHERE Condition along with JOINS
Using WHERE condition on an INNER JOIN-ed set makes no sense, when you can possibly filter out your resulting dataset by using the same condition as Table joining condition. Which is again gain in performance when you have huge datasets.
This goes hand in hand with the point above – most people don’t know about the magic of the compound JOIN/ON clause. Just as with the WHERE clause, you can have multiple conditions in the ON clause of a table JOIN. By moving all relevant filtering to this ON clause (from the WHERE clause), you create smaller, intermediary cross products. This is a bit more advanced than anything else there, but if you just sit down and learn the JOIN/ON clauses, you will benefit quite easily from this.

4. Joining Tables Using Commas
This can/does create a very large cross product of the two tables before any filtering gets applied.

5. Using aggregate functions instead of window functions
Before the introduction of window functions, the only means to aggregate data in SQL was by using a GROUP BY clause along with aggregate functions in the projection. This works well in many cases, and if aggregation data needed to be enriched with regular data, the grouped query can be pushed down into a joined subquery. Very expensive operation!
Using window functions will, Lead to more readable SQL (less dedicated GROUP BY clauses in subqueries). Improve performance, as a RDBMS is likely to optimize window functions more easily.

6. Sorting data on memory
Doing operations with data on memory is always expensive.
If you sort any SQL data in memory, think again if you cannot push sorting into your database.
The SQL ORDER BY clause supports many types of expressions, including CASE statements, which can be very useful for sort indirections.

Incoming search terms:

  • writing better sql
  • www tushroy hand com

Tushar Roy

Software Engineer (Backend engineer), Android Developer,

Leave a Reply

Your email address will not be published. Required fields are marked *