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.
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:
|
0 1 2 3 4 5 |
SELECT BAR.FOO FROM BAR QUALIFY BAR.FOO > 100; |
Equivalent of a simple Qualify with a subquery example:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT FOOBAR.FOO FROM( SELECT FOO FROM BAR WHERE BAR.FOO > 100 ) AS FOOBAR; |
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:
|
0 1 2 3 4 5 6 7 |
SELECT NAME ,CLASS ,TYPE FROM FOO QUALIFY (ROW_NUMBER() OVER(PARTITION BY CLASS, TYPE ORDER BY NAME) = 1; |
Equivalent of an advanced Qualify with a subquery example:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT NAME ,CLASS ,TYPE FROM( SELECT ROW_NUMBER() OVER(PARTITION BY CLASS, TYPE ORDER BY NAME) AS ROW_NUM ,NAME ,CLASS ,TYPE FROM FOO ) AS BAR WHERE ROW_NUM = 1; |
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?