Common SQL and PL/SQL Pitfalls: Insights from Real-World Experience

Over the years, I’ve encountered lots of different cases and certain Oracle SQL and PL/SQL behaviors that may look simple at first glance, yet often result in unexpected or tricky outputs in production environments. As database professionals, we sometimes tend to overlook these so-called “basic” topics; until they lead to bugs, performance issues, or confusing results during critical operations.

Although these topics are taught early in SQL and PL/SQL training, they are easy to forget or underestimate. That’s why I believe it’s worth revisiting them with real-world examples and explanations. By documenting these issues, I hope to help others avoid similar pitfalls and to remind myself to stay sharp as well.

This series is a reflection of what I’ve learned on the job. My goal is to contribute back to the Oracle community by sharing the kind of practical knowledge that comes only from hands-on experience.

Here’s a quick recap of the topics I’ll cover. I plan to use this blog post as a master note and will add new entries to the list below if they fall under the same theme. Of course, the common mistakes are not limited to these, there are many more. These are simply the ones I’d like to mention.

Part 1: NVL and DECODE: Lazy vs Eager Evaluation
In this post, I explored how NVL, DECODE, and CASE handle evaluation(lazy vs eager/strict) differently. Understanding their short-circuit behavior is essential, especially when these functions are used with expressions that might raise errors (e.g., division by zero or subqueries). Misunderstanding this behavior can easily lead to unexpected exceptions.

Part 2: Scalar Subquery Caching Behavior in a SQL Statement
This article dives into how scalar subqueries are cached during SQL execution. While scalar subqueries can be a simple, low-effort performance technique, their behavior can significantly impact both performance and query logic.

Part 3: Why NO_DATA_FOUND Behavior Differs in SQL and PL/SQL
Here, I discussed a common confusion among developers: the difference in behavior between SQL and PL/SQL when a SELECT INTO returns no rows. In PL/SQL, it raises a NO_DATA_FOUND exception but not in SQL contexts. Failing to anticipate this difference can result in missed exception handling and unexpected control flow.

Part 4: How SQL Handles No Matching Rows in Aggregation
This topic looks at how aggregate functions behave when no matching rows exist. A SUM() without a GROUP BY will return 1 row with NULL, whereas using GROUP BY with no matching data may return no rows at all. This subtle behavior can impact reports and business logic, especially when you expect a 0 instead of NULL or an empty result set.

Part 5: Avoid Misusing LEFT JOIN in SQL Queries
This post highlights two common pitfalls: using a LEFT JOIN when an INNER JOIN would be more appropriate, and placing filter conditions incorrectly – especially in the WHERE clause instead of the JOIN.

You might ask, “Aren’t these just minor technical details?” But in practice, these nuances can lead to major problems, such as:

• Wrong Results
• Queries that break with unhandled exceptions
• Poor performance due to misunderstood query caching behavior
• Logic errors in applications that assume PL/SQL and SQL behave identically

In my experience as a DBA, these are not rare edge cases, they are real problems I’ve encountered while managing production systems, helping developers debug logic, or investigating performance regressions after code changes.

Footnote:

For every application running on the database, typically after 1 to 3 months of usage, I conduct a necessary vs. unused index analysis based on Maria Colgan’s blog post “How to identify which indexes can be safely dropped in Oracle” and Jonathan Lewis’s Index Usage series (a set of three blog posts). As a note of caution, I don’t rely solely on the DBA_INDEX_USAGE view to assess index activity, because it does not track indexes used during referential integrity checks (such as foreign key reference indexes, which are crucial for avoiding enq: TM contentions), nor does it capture usage of domain indexes.

Additionally, I identify columns that should be marked as NOT NULL to give the optimizer the best possible chance to make the right decisions. This approach follows the guidance provided in Connor McDonald’s blog post, “NULLs vs NOT NULLs and Performance.“

Hope it helps.