Teradata’s Secret SQL | Qualify Clause

Teradata’s Qualify clause which, at the time of writing this and probably for the foreseeable future, only exists in Teradata, and is spectacularly awesome! Also, so are Koalas. 

Teradata's Secret Clause | Koalafy By
Meme of a koala poking fun at Teradata’s Qualify By clause.

What is a Qualify By clause?

To put it simply, a Qualify clause is a subquery, or more typically, it’s an analytical subquery; To put it another way, It’s a way to use simple conditional statements or complex analytical window functions to limit data without having to create a nested subquery. Something like a where clause with less, cleaner code.

Simple Qualify example:

Equivalent of a simple Qualify with a subquery example:

Notice that the second example is nothing more than a sub query, while the first example is the exact same thing but using the qualify by clause; The only thing you save is a few extra lines of code and a bit of strain on the eyes when reading the qualify by example. It’s also important to note that the explain plan of both of these queries is exactly the same.

Advanced Qualify example:

Equivalent of an advanced Qualify with a subquery example:

Both of these queries select the first NAME for each grouping of CLASS and TYPE. They also produce the exact same explain plan.

Hopefully, you’ll notice that these examples illustrate the cleanliness and simplicity that this clause can provide. Just think of it as a powerful where clause.

Do you think this is a feature you’d like to see in other DBMS’s?