An introduction to HANA’s Except Set operator function

[former_member201183]

In this blog, I would like to provide a basic introduction to the Except set operator function which has been introduced with HANA 1.0 SPS07. This blog provides basic understanding of what is Except set operator function and how to write a simple Except set operator function. On following this blog, I feel any reader would be able to write simple Except set operator function.

Let me first try to explain the use of the SQL EXCEPT clause/operator -  the Except set operator function  is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. In other words, it only returns rows from the first SELECT, which are not available in the second SELECT statement.

The syntax of Except set operator function is:

     SELECT column1 [, column2] FROM table1 [, table2] [WHERE condition]

     EXCEPT

     SELECT column1 [, column2] FROM table1 [, table2] [WHERE condition]

For Example:

The above SQL returns all artnr except the artnr for which kndnr = 255.

I feel after reading this blog, the readers would be able to use EXCEPT in situations, where ‘minus’ operation needs to be performed. I would encourage the readers to try out EXCEPT set operator.  The readers could also go through my introductory blog on GROUPING SET function and how to use it in HANA system.