Page 1 of 1
Vertica 6.1 supports INTERSECT, MINUS and EXCEPT
Posted: Wed Dec 12, 2012 4:45 pm
by JimKnicely
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)
Re: Vertica 6.1 supports INTERSECT, MINUS and EXCEPT
Posted: Tue Mar 05, 2013 10:14 am
by srinivas.martha
Hi,
Iam new to vertica,
Can anyone explain in detail difference between MINUS and EXCEPT
Thanks in advance
Srinivas
Re: Vertica 6.1 supports INTERSECT, MINUS and EXCEPT
Posted: Tue Mar 05, 2013 12:37 pm
by JimKnicely
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)
Re: Vertica 6.1 supports INTERSECT, MINUS and EXCEPT
Posted: Mon Apr 08, 2013 11:05 am
by srinivas.martha
Thanks for your reply...
So both Except and Minus are same...
Re: Vertica 6.1 supports INTERSECT, MINUS and EXCEPT
Posted: Mon Apr 08, 2013 2:29 pm
by JimKnicely
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
