Michael McLaughlin’s Oracle 11g & MySQL 5.6 Developer Handbook points out several ways Oracle developers can make their lives easier. One chapter focuses
on SQL querying and singles out some time-saving, speed-gaining tricks, like using keywords and software development tools.
SearchOracle.com recently caught up with McLaughlin to find out more about how skilled use of Oracle queries can benefit developers.
What are some common errors and problems that developers face when querying?
Michael McLaughlin: Common errors are design issues more than they are syntax errors. SQL design issues create queries that return inconsistent, incorrect or inefficient results. Inconsistent or incorrect results frequently occur because join conditions fail to resolve a relationship or WHERE clause conditions fail to filter rows correctly.
Inefficient queries generally rely on inefficient joins that may disable or use unnecessary
indexes, or they may rely on correlated queries, where the join statement matches non-unique column
values. A fix to the non-uniquely matched correlated queries is simple, because you use a look up operator like the IN, =ANY or =SOME,
all of which return results faster for non-uniquely matched column values.
What are the benefits of keyword use when querying?
McLaughlin: Keywords cover several roles in SQL and are effective when they help
developers visualize data or SQL syntax. Keywords such as INNER, LEFT,
RIGHT and FULL JOIN qualify the relationship among tables and generally help
developers visualize which table provides what data. For example, an INNER JOIN or
JOIN is the intersection of tables. It requires that you identify the columns that support
the join operation immediately following the statement. In ANSI
92, the WHERE clause only contains statements that filter the return set on non-join
conditions. The older ANSI 89 style separates tables with commas and lets join statements appear
anywhere in the WHERE clause. The WHERE clause then contains join statements and
LEFT, RIGHT and FULL JOIN statements let you perform outer joins. A LEFT JOIN takes everything on the left plus the intersection, which is generally clearer than the older Oracle alternative of using "+" symbols. That's because LEFT or RIGHT lets you visualize where the non-intersection data is located. On the other hand, a "+" is put on the side where there won't be any data outside of the intersection. It only makes sense to some developers when you explain set theory and mechanics and that makes it more mathematical than helpful.
Could you explain the importance of table aliases and why developers should use them?
McLaughlin: Table aliases, like table names, disambiguate same-named columns when they're returned by a query. Table aliases often shorten the typing of long, descriptive names. They're essential when you join copies of the same tables because the table alias enables multiple copies of tables in a query.
What are the benefits and drawbacks of using software development tools compared with using the command-line interface?
McLaughlin: There are more benefits to using software development tools than there are
drawbacks. They're great to prototype queries as well as insert, update and delete statements in
isolation. They also let you package and run script files. Ultimately, you sometimes need to
isolate a problem, and the command-line interface provides the bare bones of SQL interacting with
the database. The command line is also the lingua franca (or, common language) for SQL,
which means you can discuss SQL without needing to teach the user how to use a specific software
development tool. More or less, the command-line approach to problems neutralizes the often strong
preferences for software development tools among users.
What are two or three programming habits developers should adopt even though they might seem like extra work on the front end?
They should pick a style for writing SQL statements that lets them sight read their code for punctuation and syntax compliance. It often means a little more typing, but this can dramatically save time when looking for a stray comma or keyword. For example, a great technique places commas on the left in SELECT and ORDER BY clauses and logical AND/OR keywords on the left of the WHERE clause.
Always use table aliases, because it's easier to add content with them than it is to retroactively add content and table aliases later to disambiguate same name columns. Other techniques involve always using the AS keyword before column aliases to help clarify that the column name is represented "as" the alias. You can practice indentation patterns on CASE statements.
Michael McLaughlin is a professor of computer information technology at Brigham Young University-Idaho and an Oracle ACE. He has worked for Oracle in consulting, support and development and is the author of six books on Oracle technology.