SQL language has been around for so long, that some people feel it never changes. This is obviously not true – SQL is a dynamic language that changes all the time: some changes come from the vendors themselves but often this is the result of the standard changing and Oracle adopting the new standard in a later version.

Let’s start with a short history lesson. As we all know, SQL is not a unique language for Oracle. It’s a common language with standards set by ANSI. The first version of the language standardization was presented back in 1986 with the issuance of SQL86. In 1992, a revised version of the language came out (SQL-92). When Oracle tried to implement this standard at full, they noticed that some of the things there are really different from how Oracle did things so it adopted only a part of the standard in Oracle RDBMS 8 (and 8i).

In 1999 came a new version and which extended the language even more (SQL-99). This version included many of the features that Oracle had implemented before (triggers, recursive sql, etc.) so Oracle adapted themselves to the standard and were fully compliant to the new standards by the time Oracle 9i version came out (if memory serves me right, it was version 9.2).

Now this was a huge different in the way we referred to SQL. No more “Oracle” SQL vs. “other vendor” SQL – now there is a standard which made it easier for developers and DBAs to write portable queries. This change was massive but some people just overlooked it and kept using the old syntax.

In 2003, a new standard was released (which included the merge command, XML definitions, and Window queries for example) and Oracle 10g was adjusted accordingly. In 2008 came another version of the standard and Oracle 11.2 was fully compatible with the ANSI standard SQL 2008, and extends the language again. In 2011, a new ANSI SQL version was release and Oracle 12c was adjusted again (it’s not fully implemented – some of the things are SQL-2008 and some are SQL-2011 – see the documentation for more details).

Even though Oracle tried so keep up with the standards, some of the things were developed by Oracle before the standard existed or Oracle fulfilled the standard but didn’t used the original ANSI syntax – so some of the things might look and feel different between vendors. Oracle also introduces some features which are not standard to achieve some advantage over its competitors so look out for pitfalls like that.

One example I’d like to show here for a change in Oracle syntax was the SQL-99 change to the inline views. This, for some reason, was a change that most developers choose to overlook but it makes writing complex SQL queries much easier.

When using the “WITH” syntax, we can create an inline view (based on a query we provide) and then use that view in the boundaries of that query.
Why this is good for? When we want to repeat a certain query a few times within our query, we can either use the old inline view and repeat our code, or define a WITH block to create a more simple representation of our view. We can also hint the optimizer that the WITH block might be called multiple time and it will create a materialized result set so we won’t have to query the data again and again.

How does it look when using it in a query?

WITH
   subquery_name
AS
  (inline view SQL statement)
SELECT
  (query naming subquery_name);

In rows 1-4 we define the view and give it a name,
In line 5 we use the name as if it was a real view in the database.

This post is quite old – it was originally posted back in 2010 and I’m reviewing it now since there has been another change how WITH syntax work. In Oracle 11g, we could use the with command to replace hierarchical queries (and using recursive queries instead). Oracle 12c add new feature were we could add PL/SQL code (functions) which will only be available in the boundaries of our query – and that’s  without the need to create a PL/SQL code segment in our database. I will publish a different post on this later – once I’ll get around to translate it.. 🙂