10 useful tips for writing SQL queries

Today I want to talk about the simple things that will help novice SQL programmers more correctly, thoughtfully and simply make the logic of their queries and learn SQL faster.

All of these points I highlight as “useful tips”, which are based on both personal experience and that of other more experienced people. And today I want to share them with you. I got 10 of them.

Tip 1 – list the fields

When writing a request, list the fields. Do not put *, as in the future there may be some problems, although at first sight you do not notice them, but on the contrary, you think it is much more convenient.

Problems may arise as follows: indexes are not used, columns with the same name appear, this occurs when in a query you combine several tables that have fields with the same name.

And in this case, for example, the developer of the client part can then, without suspecting it, output to the user not the data that is needed.

You do not need to do this.

SELECT *
FROM table

It’s better this way:

SELECT col, col2, col3
FROM table

Note! If you are new to SQL at all, you can read the article on the basics of the SELECT statement.

Tip 2 – do not write complex multi-level queries

Don’t make complex, big, multi-level requests. You will simply get confused, as such a query is difficult to rework, and in general – it affects the speed of work.

If the query is used frequently, then take the most complex part into a function or procedure, and in general, feel free to write your own functions for any needs, of course, if it is assumed that these functions will be actively used in all sorts of queries, and in a query in the right place call it.

This greatly simplifies the writing of queries, in the future you will not have to study the logic of the query anew, for example, if you need to remake something in it, and in some cases the speed of the query will increase.

Tip 3 – do not use functions in WHERE

Do not use functions in the condition that will calculate a value based on the field in the current table, as this will be done for each row, and thus increase the load and, consequently, reduce the speed. This also applies to subqueries.

Although in many cases you can’t do without it, but remember, if you can think and not use it, it’s better to think and implement the task differently. All this can be attributed to the SELECT section, for example, but it is required quite often.

Tip 4 – list the columns during INSERT

When adding data to a table, as well as in SELECT, list the table fields, for example:

You don’t have to do that:

INSERT INTO table
VALUES (col1, col2, col3...)

It’s better this way:

INSERT INTO table (col1, col2, col3...)
VALUES (col1, col2, col3...)

Since fields can be added to the table, there may be some difficulties, even if you have taken care of the default value for the new column.

Tip 5 – use the default value, avoid NULL

This advice follows from the previous one. Try to set the default values when creating a table to get rid of NULL, because if you do not do this, you may have values such as NULL, and you should remember that NULL is not a value, and this is the state of the cell, ie no value.

What is so bad is this value, but that when writing queries, you have to remember that you may have such values and take them into account accordingly.

Because if you forget or simply do not know that in some table there are values NULL, then later you can display incorrect data, and this is not good.

Tip 6 – in ORDER BY use column names

When sorting (ORDER BY), it is better to use column names rather than column position(s) as problems may occur.

For example, you used sorting by their number and gave the request to the application developer, then after a while you had to change the request by adding or deleting some fields, and you forgot about sorting and gave the request to the developer again.

And now in the output of data will be used incorrect sorting, and from the user’s side in general, you may think that the output is wrong data, he will tell you about it, and you will not understand, because it worked, what is wrong.

And all because no error will occur, so that it can be tracked, and all trite and simple, you used the sorting by column number.

You don’t have to do that:

SELECT col, col2, col3
FROM table
ORDER BY 2, 1

Or better yet:

SELECT col, col2, col3
FROM table
ORDER BY col2, col

Tip 7 – save SQL queries

If you write a one-time request and it can and will never be used again, still save the request in any form, usually in .sql files, as experience shows that it can be useful for other tasks or help to solve them much faster.

If you don’t save it, you will have to think through all the logic, the algorithm again, which of course affects the speed of the task.

Tip 8 – do not create many cursors

Don’t create too many cursors. Since a cursor is quite a resource-intensive operation for a server, it is better not to use it if you can do without it.

Although there are tasks where the cursor is the only possible solution, so it is also, in some cases, very useful.

Tip 9 – check and test conditions at UPDATE and DELETE

The council for its own scrutiny. Sometimes when you need something massively updated (UPDATE), always test the request on a test base, otherwise, for example, one wrong condition may be almost irreversible.

Therefore, you should always have a test base at hand.

Tip 10 – use clear names

Create clear column names in tables, and use clear variable names, this advice applies not only to SQL, but also to programming in general.

I hope these tips will help you make your queries right and do not step on the same rake as many other novice programmers.