Skip to Content
Author's profile photo Rupali Arora

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]

     EXCEPT

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

For Example:

Except.png

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.

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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.

      Regards,

      Justin

      Author's profile photo Lars Breddemann
      Lars Breddemann

      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

      Author's profile photo Former Member
      Former Member

      Right, that was my conclusion as well. Which is why I don't see it as a big deal to mention that EXCEPT was included in SPS07 since MINUS was already available.

      Regards,

      Justin

      Author's profile photo Rupali Arora
      Rupali Arora
      Blog Post Author

      Thanks for your feedback Justin. I would look at it.