Skip to Content

An introduction to HANA’s Except Set operator function

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]


     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.

You must be Logged on to comment or reply to a post.
  • Why would one use an EXCEPT operator vs. a MINUS operator, they perform the exact same function as I have just tested with your example.



    • Hi Justin,

      MINUS is not SQL standard, EXCEPT is.

      Technically both commands do the same in SAP HANA.

      Since MINUS is used in other DBMS I assume it had been included to ease migration towards SAP HANA.

      – Lars