Best Practices for Writing Oracle SQL

Writing efficient and high-quality SQL is hard to do. Sometimes it comes down to trialling different types of queries to get one that gives you the results you want and has good performance. There are a range of ‘best practices’ or tips that are recommended for working with SQL. Many of them relate to SQL overall, and some of them are specific to Oracle SQL.

In general, Oracle SQL best practices include these techniques:

Use ANSI Joins Instead of Oracle Joins

In Oracle SQL, there are two ways to join tables. You might be familiar with the ANSI method, which involves using JOIN keywords between tables:

SELECT emp.*, dept.* FROM emp INNER JOIN dept ON emp.dept_id = dept.id;

You can also do outer joins such as a LEFT JOIN:

SELECT emp.*, dept.* FROM emp LEFT JOIN dept ON emp.dept_id = dept.id;

There is another method which is occasionally referred to as an Oracle join, because the syntax is Oracle-specific. An inner join is done using the WHERE clause:

SELECT emp.*, dept.* FROM emp, dept WHERE emp.dept_id = dept.id;

An outer join is done by placing a (+) on the WHERE clause after the column that NULLs are allowed. For example, a LEFT JOIN can be written as:

SELECT emp.*, dept.* FROM emp, dept WHERE emp.dept_id = dept.id(+);

A RIGHT JOIN can be written by putting the symbol on the other side of the join:

SELECT emp.*, dept.* FROM emp, dept WHERE emp.dept_id(+) = dept.id;

The recommendation with writing joins is to use the ANSI style (the JOIN and ON keywords) rather than the Oracle style (the WHERE clause with (+) symbols). I’ve written about this before in my guide to joins in Oracle, and there are a few reasons for this:

• In large queries, it’s easy to forget to add a WHERE clause to join a table, causing unnecessary cartesian joins and incorrect results

• The WHERE clause should be used for filtering records, not for joining tables together. A subtle difference, but it makes the query easier to understand

• ANSI joins are arguably easier to read, as you can see which section is used for joins and which is used for filtering data.

Avoid WHERE Clauses with Functions

Another recommendation for working with Oracle SQL is to avoid writing WHERE clauses that use functions. In SQL, WHERE clauses are used to filter the rows to be displayed. These are often used to check that a column equals a certain value:

WHERE status = ‘A’

You may have a need to compare a column to a value that has used a function. For example:

WHERE UPPER(last_name) = ‘SMITH’

Another example could be:

WHERE ROUND(monthly_salary) > 2000

Using functions on columns in the WHERE clause should be avoided. This is because any indexes that are created on the columns themselves (e.g. last_name or monthly_salary) will not be used if a function is applied in the query, which can slow the query down a lot.

Minimize the Use of DISTINCT

It can be tempting to add a DISTINCT keyword to ensure you don’t get duplicate records. But adding a DISTINCT keyword will likely cause an expensive operation to be performed on your query, slowing it down. It will give you the results you need, but it’s masking a problem elsewhere. It could be from an incomplete JOIN, or incorrect data in a table, or some criteria you aren’t considering, which is causing the duplicate row. Fixing the issue in your query or in your data is the right solution.

UNION ALL instead of UNION

There are two similar keywords in SQL that are used to combine results: UNION and UNION ALL. They are called ‘set operators’, as they work with result sets.There are some minor differences between them. UNION ALL shows all records in both result sets, and UNION shows all records excluding duplicates.

Just to be clear, UNION removes duplicates and UNION ALL does not.

This means, in Oracle, that an extra step is performed when using a UNION to remove all duplicate rows from the result set after it is combined. It’s the same as performing a DISTINCT.

If you really need duplicates removed, then use UNION. But, if you only want to combine values and don’t care about duplicates, or want to see all values, then use UNION ALL. Depending on your query, it will give you the same results and also perform better as there is no duplicate removal.

Use Table Aliases

A great way to improve your queries is to use table aliases. Table aliases are names you can give to tables in your queries, to make them easier to write and work with.

Only Use HAVING on Aggregate Functions

The HAVING clause in Oracle SQL is used to filter records from your result set. It’s very similar to the WHERE clause. However, the WHERE clause filters rows before the aggregate functions are applied, and the HAVING clause filters rows after the aggregate functions are applied. It can be tempting to use HAVING for everything if you’re using an aggregate function, but they do different things in your query.

For example:

SELECT status, COUNT(*)

FROM product

WHERE status IS NOT NULL

GROUP BY status

HAVING COUNT(*) > 1;

This will find the count of each product status that is not NULL where there is more than one record for the status, which is likely what you want. If you write the query using only the HAVING clause, it would look like this:

SELECT status, COUNT(*)

FROM product

GROUP BY status

HAVING status IS NOT NULL

AND COUNT(*) > 1;

This may give you different results, depending on your data. It may also perform worse, as it needs to aggregate all of the data before removing it using the HAVING clause. It also implies a different set of rules.

Be sure to only use HAVING on aggregate functions and use WHERE on results you want to restrict before the aggregate.

Conclusion:

So, there are my best practices for working with Oracle SQL. Some of them are applicable to all types of SQL, but many of them are Oracle specific.