Skip to Content

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.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Justin Molenaur

    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

    (0) 
    1. 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

      (0) 
      1. Justin Molenaur

        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

        (0) 

Leave a Reply