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:
- Always count the number of right facing parentheses and the number of left facing parentheses within the SELECT statement.
- Always make sure that all regular expressions (non-group functions) in the SELECT clause are also listed in the GROUP BY clause.
- Always check to make sure that you have one less join condition in the WHERE clause than you have items listed in the FROM clause.
It is very common to use parentheses in a SELECT statement. They are used for a multitude of reasons. Some of these are:
- To designate the proper order of operator execution in a calculation
- To control the logic in a WHERE clause
- To designate a sub-query
- To hold the parameters for a function
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.
Listing 1. A SELECT statement with SELECT and GROUP BY
SQL> select fk_department, last_name, first_name, sum(tool_cost) 2 from employee, emp_tools 3 where payroll_number = fk_payroll_number 4 group by fk_department; select fk_department, last_name, first_name, sum(tool_cost) * ERROR at line 1: ORA-00979: not a GROUP BY expression
Listing 2. Cartesian and Non-Cartesian Select Statements:
SQL> select department_name, count(*) 2 from department, employee, emp_tools 3 group by department_name; DEPARTMENT_NAME COUNT(*) --------------- --------- CENSUS DEPT 416 INTERIOR DESIGN 416 POLITICAL SCIEN 416 TRESURY DEPAR 416 WELFARE BUREAU 416 SQL> select department_name, count(*) 2 from department, employee, emp_tools 3 where department = fk_department 4 and payroll_number = fk_payroll_number 5 group by department_name; DEPARTMENT_NAME COUNT(*) --------------- --------- INTERIOR DESIGN 11 POLITICAL SCIEN 9 WELFARE BUREAU 6
About the Author
John Palinski has been an Oracle developer and project manager for the last fourteen years. He developed one of the first relational database work management systems in the country. Mr. Palinski also teaches his own Oracle courses at Iowa Western Community College and the University of Nebraska at Omaha. Mr. Palinski is the author of the Oracle Database Construction Kit published by QUE and provides custom Oracle training worldwide through his consulting business, Realistic Software Training.
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our Oracle and SQL gurus are waiting to answer your toughest questions.