# NVL vs NULLIF

What’s the difference between NVL and NULLIF?

These two similarly named functions do very different things. Here’s a comparison.

# Quick start guide to 10046 tracing

Here’s a quick start guide to taking and interpreting 10046 trace, with bind variables.

# Tip: skip the TO_DATE with ANSI DATE literals

Tired of all the typing required to specify a date in a query using TO_DATE?

```SELECT SUM(sale_amt) AS december12_sales
WHERE sale_date BETWEEN to_date('2012-12-01','YYYY-MM-DD') AND to_date('2012-01-01','YYYY-MM-DD');```

Whew! Shake out your fingers, that was a pain. Fortunately, there’s an easier way. Read more

# Recursive WITH, part III: IS_LEAF

The CONNECT BY syntax provides a useful pseudocolumn, CONNECT_BY_ISLEAF, which identifes leaf nodes in the data: it’s 1 when a row has no further children, 0 otherwise. In this post, I’ll look at emulating this pseudocolumn using recursive WITH.
Read more

# Recursive WITH, part II: Hierarchical queries

In my last post, I looked at using recursive WITH to implement simple recursive algorithms in SQL. One very common use of recursion is to traverse hierarchical data. I recently wrote a series of posts on hierarchical data, using Oracle’s CONNECT BY syntax and a fun example. In this post, I’ll be revisiting the same data using recursive WITH.
Read more

# Recursion with recursive WITH

I recently had the opportunity to talk with Tom Kyte (!), and in the course of our conversation, he really made me face up to the fact that I'm not making much use of the developments in Oracle SQL since 8i. In this blog post, I'll look at the recursive subquery factoring introduced in Oracle 11g.

# CONNECT BY, Part II

In my last post, I used CONNECT BY to query a table holding the bones of the skeleton in the “Dem Dry Bones” song: since the skeleton in the song has a head with no parent bone (making it a root node), and each bone has only one parent bone, it’s hierarchical data.

In this post, I’m going to add an arm and some ribs to the skeleton to make the data more interesting to query.
Read more

# CONNECT BY, Part I

In my last post, I briefly covered what hierarchical data is and how it’s represented in an RDBMS using the adjacency list model. In this post, we’ll look at querying hierarchical data with Oracle SQL’s CONNECT BY construct, using a really fun example.

# Hierarchical Data

This is the first in a series of posts about Oracle SQL and hierarchical data.

# Oracle’s outer join syntax (+)

Sometimes I think I might be the only person who’s more comfortable with Oracle’s original outer join syntax than with the ANSI standard. Read more