As developers, we can often be dogmatic about our choice and usage of programming languages. Even if there may be a better tool suited for the specific task at hand, we can sometimes get carried away with wanting to use our favorite language for everything.
One instance in which this can occur is when you’re working with a SQL Database for your application.. While it’s entirely possible to access the data from a database, and then perform manipulations on it using the programming language you’re working with; a much better approach is to leverage SQL’s ability to perform those manipulations whenever it’s possible to do so.
Some examples of when you may want to use SQL directly for processing data, rather than the programming language you’re writing your application in, are:
- Summing data
- Calculating the average of a set of data
- Sorting data with a custom formula
- String manipulation, such as concatenation
- For example, getting the full name of a user from the FIRST_NAME and LAST_NAME properties
For example, if you were using SQL to concatenate the full name of a user using their FIRST_NAME and LAST_NAME entries in a database, you could do so using this query:
Doing this gets you the data that you need to present to the rest of your application directly from the SQL database, without having to do extra data manipulation on formatting with the programming language that you’re using to write your app.
If these data manipulation operations are all written using SQL itself, then the data will be in a correct and consistent format regardless of the language that is being used to access that data. You won’t have to rewrite these operations in a different programming language, because they will be in SQL from the start.
Using SQL directly to access and manipulate the data you need from your database to present to your application ensures that the query logic will be consistent across future application rewrites, will work across multiple platforms, and will always be presented in a consistent format.