Vertica 6.1 supports INTERSECT, MINUS and EXCEPT

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1802
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Vertica 6.1 supports INTERSECT, MINUS and EXCEPT

Post by JimKnicely » Wed Dec 12, 2012 4:45 pm

Great news! The latest version of Vertica (6.1) now supports the INTERSECT, MINUS and EXCEPT set operators.

Below are several examples using the following data:

Code: Select all

dbadmin=> SELECT name FROM dba_team;
  name
--------
Peng C
Jim K
Tom L
Pat K
(4 rows)

Code: Select all

dbadmin=> SELECT name FROM bi_team;
   name
-----------
Dan J
Jessica D
Jerry M
Bill K
Tom K
Susan Y
Jessica W
Jim K
(8 rows)
1. INTERSECT example:

Code: Select all

dbadmin=> SELECT name FROM dba_team
dbadmin-> INTERSECT
dbadmin-> SELECT name FROM bi_team;
name
-------
Jim K
(1 row)
2. MINUS example:

Code: Select all

dbadmin=> SELECT name FROM bi_team
dbadmin-> MINUS
dbadmin-> SELECT name FROM dba_team;
   name
-----------
Bill K
Dan J
Jerry M
Jessica D
Tom K
Jessica W
Susan Y
(7 rows)
3. EXECPT example:

Code: Select all

dbadmin=> SELECT name FROM dba_team
dbadmin-> EXCEPT
dbadmin-> SELECT name FROM bi_team;
  name
--------
Peng C
Pat K
Tom L
(3 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

srinivas.martha
Newbie
Newbie
Posts: 4
Joined: Mon Feb 25, 2013 10:34 am

Re: Vertica 6.1 supports INTERSECT, MINUS and EXCEPT

Post by srinivas.martha » Tue Mar 05, 2013 10:14 am

Hi,

Iam new to vertica,

Can anyone explain in detail difference between MINUS and EXCEPT

Thanks in advance

Srinivas

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1802
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Vertica 6.1 supports INTERSECT, MINUS and EXCEPT

Post by JimKnicely » Tue Mar 05, 2013 12:37 pm

Hi Srinivas!

According to the SQL Reference manal, the EXCEPT clause:
Combines two or more SELECT queries, returning the results of the left-hand query that are not returned by the right-hand SELECT query.

Note: MINUS is an alias for EXCEPT.
So MINUS and EXCEPT do the same exact thing :)

Example:

Code: Select all

dbadmin=> SELECT * FROM tab1;
  c1
------
 Jim
 Tom
 Peng
(3 rows)

dbadmin=> SELECT * FROM tab2;
 c1
-----
 Tom
(1 row)

dbadmin=> SELECT * FROM tab1 MINUS SELECT * FROM tab2;
  c1
------
 Jim
 Peng
(2 rows)

dbadmin=> SELECT * FROM tab1 EXCEPT SELECT * FROM tab2;
  c1
------
 Jim
 Peng
(2 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

srinivas.martha
Newbie
Newbie
Posts: 4
Joined: Mon Feb 25, 2013 10:34 am

Re: Vertica 6.1 supports INTERSECT, MINUS and EXCEPT

Post by srinivas.martha » Mon Apr 08, 2013 11:05 am

Thanks for your reply...

So both Except and Minus are same... :lol:

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1802
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Vertica 6.1 supports INTERSECT, MINUS and EXCEPT

Post by JimKnicely » Mon Apr 08, 2013 2:29 pm

Yes, they are the same!

From the SQL Reference Manual:
Note: MINUS is an alias for EXCEPT.
Note that in MS SQL Server the MINUS operator does not exist. You have to use EXCEPT :roll:
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”