SQL should avoid Query

1. Avoid using SQL IN THE antsense Operator!

"! = ": Not equal

"!> ": Not greater

"! <": Not less

"=", ">", "<" Can be directly added! Although this facilitates the use of developers, it is a pity that only MSSQL and DB2 relational databases provide support. SQL provides general operators "<>", "> =", and "<= ". It can be used in any database system. To ensure database portability and compatibility, it is best to use the general operation provided by SQLAlgorithm.

In addition to general SQL pre-algorithms, not can be used in combination with the formula. For example:

Select * from student where age> = 20
Select * from student where not (age <20)

The execution results of the two statements are the same.

2. Avoid using multiple or statements.

Slect * from student where age = 21

Or age = 22 or age = 33 or age = 35

Or age = 40 or age = 45

This is not only difficult to write, but also troublesome to maintain. If you do not pay attention to it, you will lose a value.

SQL provides the in keyword. The preceding query statement can be written in this way.

Select * from student where age in)

The results of the two query statements are the same, but the second query statement is easier to maintain.

3. Use betweem and for closed Range Query

Select * from student where age> 18 and age <60

Select * from student where age between 18 and 60

The database optimizes the query of between and. Therefore, between should be given priority in range value detection.

In addition, beween and can only be used for closed interval search. If you want to detect open or semi-open intervals, you can use other methods.