There are three simple checks that all developers should do before executing any SQL SELECT statement. Performing these checks before submitting the statement will gain you a marked increase in your productivity. They will also allow you to avoid the most common errors that occur when writing a SELECT statement. Avoiding these errors will cause you to get your results sooner by eliminating the need to re-run statements. Finally, the checks will also help you avoid wasted query time caused by retrieving incorrect records.
The three checks are:
It is very common to use parentheses in a SELECT statement. They are used for a multitude of reasons. Some of these are:
It is very common to omit one of the parentheses when writing a SELECT statement. If you do not have the same number of left and right facing parentheses, your statement will not parse. It will save you some time if you count the parentheses before executing the query.
The second most common error for a developer is not to synchronize the expressions contained in the SELECT clause with the expressions in the GROUP BY clause. All expressions listed in the SELECT clause must also be listed in the GROUP BY clause.
Listing 1 (below) illustrates a SELECT statement with this problem. Notice that the Select clause contains the non-group columns fk_department, last_name, and first_name. After reviewing the clause, it appears that the developer intends to compute the total cost of tools for each employee.
However, the GROUP BY clause only lists fk_department. This clause is telling the database manager to compute the total cost of tools for each department. This query has two different groups and confuses the database manager. That is the reason the database manager issued the error message and stopped the parsing of the statement.
Mismatched groups are a very common error. Checking the two groups will help you execute your statements quicker by eliminating the re-runs caused by this error.
The last of the three errors is the most time consuming. The reason is that this error will not stop the parsing and execution of the statement. The result of the error is incorrect data, an excessive wait for statement results, and an overuse of the CPU. This error often results in the "Query From Hell" that uses up all of the CPU. It is called a Cartesian Join.
SELECT statements have the ability to take records from multiple tables and match them using common values. The matching logic consists of a JOIN statement in the WHERE clause. For example, in Listing 2 (below) "department = fk_department" is a JOIN condition. It matches records from the Department and Employee tables. If you forget the JOIN condition, each record in the first table will be joined to each record in the second table. This is a Cartesian join.
In Listing 2, the first SELECT statement produced a Cartesian join. Notice that 2018 virtual records were processed and the result was invalid information. This resulted in a waste of the developer's time because the SELECT statement needed to be re-run. A symptom of this type of query is the overlong processing time. A simple check using the rule "A query should have one less JOIN condition than the number of tables to be joined." The second query used this rule with proper results.
Drilling yourself on the three checks discussed in this tip will increase your productivity. Remember, a developer is similar to a baseball player. Practice the mechanics and the play will come naturally. Learn and practice these techniques and you will become a better developer.
相关内容
