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 \u2018best practices\u2019 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 this article, I\u2019ll explain ten of the Oracle SQL best practices to help improve your SQL queries.<\/p>\n
In Oracle SQL, there are two ways to join tables. You might be familiar with the ANSI method, which involves using JOIN<\/strong> keywords between tables:<\/p>\n You can also do outer joins such as a LEFT JOIN:<\/p>\n 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<\/strong> clause:<\/p>\n An outer join is done by placing a (+)<\/strong> on the WHERE<\/strong> clause after the column that NULL<\/strong>s are allowed. For example, a LEFT JOIN<\/strong> can be written as:<\/p>\n A RIGHT JOIN<\/strong> can be written by putting the symbol on the other side of the join:<\/p>\n The recommendation with writing joins is to use the ANSI style (the JOIN <\/strong>and ON<\/strong> keywords) rather than the Oracle style (the WHERE<\/strong> clause with (+)<\/strong> symbols). I\u2019ve written about this before in my guide to joins in Oracle<\/a>, and there are a few reasons for this:<\/p>\n Another recommendation for working with Oracle SQL is to avoid writing WHERE clauses that use functions. In SQL, WHERE<\/strong> clauses are used to filter the rows to be displayed. These are often used to check that a column equals a certain value:<\/p>\n You may have a need to compare a column to a value that has used a function. For example:<\/p>\n Another example could be:<\/p>\n Using functions on columns in the WHERE<\/strong> clause should be avoided. This is because any indexes that are created on the columns themselves (e.g. last_name <\/em>or monthly_salary<\/em>) will not be used if a function is applied in the query, which can slow the query down a lot.<\/p>\n To avoid using a function on a column, consider if there\u2019s a way to write the WHERE<\/strong> clause without the function. Sometimes there is, but other times you need to write the function.<\/p>\n If you do need to have the function on the column in the WHERE<\/strong> clause, consider creating a function-based index on the column. This is a type of index that is created on the result of a function applied to the column, which could be used in this query.<\/p>\n I\u2019ve seen several examples of queries that are looking up a range of records based on criteria. The criteria are more than just a simple WHERE<\/strong> clause, and depending on different types of records, the joins and other criteria might be different.<\/p>\n This is often implemented as several SELECT<\/strong> queries joined together using UNION<\/strong> or UNION ALL<\/strong> keywords. For example:<\/p>\n This is a simple example, but often the different queries may include joins or lookups to other tables.<\/p>\n Structuring a query like this means that the tables need to be queried several times (once for each SELECT<\/strong> query), which is quite inefficient. There is a chance that your table will have an index on it to make it run more efficiently, but there is another method that\u2019s worth trying. Rather than having separate queries with UNION ALL<\/strong>, try putting the logic inside a CASE <\/strong>statement inside a single SELECT<\/strong>:<\/p>\n This query would only run once on the product<\/em> table and will show the same results as separate SELECT<\/em> queries with a UNION ALL<\/em>.<\/p>\n The logic to show the right records is in the CASE<\/em> statement. There are several lines, one for each set of criteria, and it returns a 1 if a match is found. This logic is all inside a subquery, and the outer query filters to show only those records where that CASE<\/strong> is 1.<\/p>\n There are a few different ways to write the CASE<\/strong> statement, but the idea is to only have the main query and several criteria in the CASE<\/strong> statement, rather than separate queries. However, make sure you test both versions of the query for performance, as there may be indexes that are used with the UNION<\/strong> query that don\u2019t run with the CASE<\/strong> query.<\/p>\n The DISTINCT<\/strong> keyword in SQL allows you to return unique records in the result set by eliminating duplicate results. This seems simple, and it\u2019s a useful command. Using DISTINCT<\/strong> is OK in many cases, however, it can be a symptom of a different issue. If your result set is displaying data from many different tables, you might end up getting some duplicate results. I\u2019ve seen this many times in my queries.<\/p>\n It can be tempting to add a DISTINCT<\/strong> keyword to ensure you don\u2019t get duplicate records. But adding a DISTINCT<\/strong> 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\u2019s masking a problem elsewhere. It could be from an incomplete JOIN<\/strong>, or incorrect data in a table, or some criteria you aren\u2019t considering, which is causing the duplicate row. Fixing the issue in your query or in your data is the right solution.<\/p>\n Occasionally you may need to write queries that use several values as criteria. This is often done as a WHERE<\/strong> clause and an IN<\/strong> keyword:<\/p>\n This query might give you the results you want. What would happen if the status values change at some point in the future, or the business rules change which means you need to adjust this list.<\/p>\n If this list is coded into your query, you\u2019ll need to adjust your query. This may result in change in application code and a deployment process.<\/p>\nSELECT emp.*, dept.*\r\nFROM emp\r\nINNER JOIN dept ON emp.dept_id = dept.id;<\/pre>\n
SELECT emp.*, dept.*\r\nFROM emp\r\nLEFT JOIN dept ON emp.dept_id = dept.id;<\/pre>\n
SELECT emp.*, dept.*\r\nFROM emp, dept\r\nWHERE emp.dept_id = dept.id;<\/pre>\n
SELECT emp.*, dept.*\r\nFROM emp, dept\r\nWHERE emp.dept_id = dept.id(+);<\/pre>\n
SELECT emp.*, dept.*\r\nFROM emp, dept\r\nWHERE emp.dept_id(+) = dept.id;<\/pre>\n
\n
2. Avoid WHERE Clauses with Functions<\/h2>\n
WHERE status = \u2018A\u2019<\/pre>\n
WHERE UPPER(last_name) = \u2018SMITH\u2019<\/pre>\n
WHERE ROUND(monthly_salary) > 2000<\/pre>\n
3. Use CASE Instead of Multiple Unions<\/h2>\n
SELECT id, product_name\r\nFROM product\r\nWHERE status = \u2018X\u2019 AND created_date < TO_DATE(\u20182017-01-01\u2019, \u2018YYYY-MM-DD\u2019)\r\nUNION ALL\r\nSELECT id, product_name\r\nFROM product\r\nWHERE status = \u2018A\u2019 AND product_series = \u2018WXT\u2019;<\/pre>\n
SELECT id, product_name\r\nFROM (\r\nSELECT id, product_name,\r\nCASE\r\nWHEN status = \u2018X\u2019 AND created_date < \r\n TO_DATE(\u20182017-01-01\u2019, \u2018YYYY-MM-DD\u2019) THEN 1\r\nWHEN status = \u2018A\u2019 AND product_series = \u2018WXT\u2019 THEN 1\r\nELSE 0 END AS prodcheck\r\nFROM product\r\n) sub\r\nWHERE prodcheck = 1;<\/pre>\n
4. Minimise the Use of DISTINCT<\/h2>\n
5. Redesign Data Value Lists to Use Tables<\/h2>\n
SELECT *\r\nFROM product\r\nWHERE status IN (\u2018A\u2019, \u2018P\u2019, \u2018C\u2019, \u2018S\u2019);<\/pre>\n