EXCEPT clause

Combines two or more SELECT queries.

Combines two or more SELECT queries. EXCEPT returns distinct results of the left-hand query that are not also found in the right-hand query.

Syntax

SELECT
    EXCEPT except-query[...]
    [ ORDER BY { column-name  | ordinal-number } [ ASC | DESC ] [,...] ]
    [ LIMIT { integer | ALL } ]
    [ OFFSET integer ]

Notes

  • Use the EXCEPT clause to filter out specific results from a SELECT statement. The EXCEPT query operates on the results of two or more SELECT queries. It returns only those rows in the left-hand query that are not also present in the right-hand query.

  • Vertica evaluates multiple EXCEPT clauses in the same SELECT query from left to right, unless parentheses indicate otherwise.

  • You cannot use the ALL keyword with an EXCEPT query.

  • The results of each SELECT statement must be union compatible. Each statement must return the same number of columns, and the corresponding columns must have compatible data types. For example, you cannot use the EXCEPT clause on a column of type INTEGER and a column of type VARCHAR. If statements do not meet these criteria, Vertica returns an error.

  • You can use EXCEPT in FROM, WHERE, and HAVING clauses.

  • You can order the results of an EXCEPT operation by including an ORDER BY operation in the statement. When you write the ORDER BY list, specify the column names from the leftmost SELECT statement, or specify integers that indicate the position of the columns by which to sort.

  • The rightmost ORDER BY, LIMIT, or OFFSET clauses in an EXCEPT query do not need to be enclosed in parentheses, because the rightmost query specifies that Vertica perform the operation on the results of the EXCEPT operation. Any ORDER BY, LIMIT, or OFFSET clauses contained in SELECT queries that appear earlier in the EXCEPT query must be enclosed in parentheses.

  • Vertica supports EXCEPT noncorrelated subquery predicates. For example:

    => SELECT * FROM T1
   WHERE T1.x IN
      (SELECT MAX(c1) FROM T2
       EXCEPT
          SELECT MAX(cc1) FROM T3
       EXCEPT 
          SELECT MAX(d1) FROM T4);

Examples

Consider the following three tables:

Company_A

  Id  | emp_lname |      dept      | sales
------+-----------+----------------+-------
 1234 | Stephen   | auto parts     |  1000
 5678 | Alice     | auto parts     |  2500
 9012 | Katherine | floral         |   500
 3214 | Smithson  | sporting goods |  1500
(4 rows)

Company_B

  Id  | emp_lname |    dept     | sales
------+-----------+-------------+-------
 4321 | Marvin    | home goods  |   250
 8765 | Bob       | electronics | 20000
 9012 | Katherine | home goods  |   500
 3214 | Smithson  | home goods  |  1500
(4 rows)

Company_C

  Id  | emp_lname |      dept      | sales
------+-----------+----------------+-------
 3214 | Smithson  | sporting goods |  1500
 5432 | Madison   | sporting goods |   400
 7865 | Cleveland | outdoor        |  1500
 1234 | Stephen   | floral         |  1000
(4 rows)

The following query returns the IDs and last names of employees that exist in Company_A, but not in Company_B:

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT id, emp_lname FROM Company_B;
  id  | emp_lname
------+-----------
 1234 | Stephen
 5678 | Alice
(2 rows)

The following query sorts the results of the previous query by employee last name:

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT id, emp_lname FROM Company_B
   ORDER BY emp_lname ASC;
  id  | emp_lname
------+-----------
 5678 | Alice
 1234 | Stephen
(2 rows)

If you order by the column position, the query returns the same results:

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT id, emp_lname FROM Company_B
   ORDER BY 2 ASC;
  id  | emp_lname
------+-----------
 5678 | Alice
 1234 | Stephen
(2 rows)

The following query returns the IDs and last names of employees that exist in Company_A, but not in Company_B or Company_C:

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT id, emp_lname FROM Company_B
   EXCEPT
   SELECT id, emp_lname FROM Company_C;
  id  | emp_lname
------+-----------
 5678 | Alice
(1 row)

The following query shows the results of mismatched data types:

=> SELECT id, emp_lname FROM Company_A
   EXCEPT
   SELECT emp_lname, id FROM Company_B;
ERROR 3429:  For 'EXCEPT', types int and varchar are inconsistent
DETAIL:  Columns: id and emp_lname

Using the VMart example database, the following query returns information about all Connecticut-based customers who bought items through stores and whose purchases amounted to more than $500, except for those customers who paid cash:

=> SELECT customer_key, customer_name FROM public.customer_dimension
      WHERE customer_key IN (SELECT customer_key FROM store.store_sales_fact
         WHERE sales_dollar_amount > 500
         EXCEPT
         SELECT customer_key FROM store.store_sales_fact
         WHERE tender_type = 'Cash')
      AND customer_state = 'CT';
 customer_key |    customer_name
--------------+----------------------
        15084 | Doug V. Lampert
        21730 | Juanita F. Peterson
        24412 | Mary U. Garnett
        25840 | Ben Z. Taylor
        29940 | Brian B. Dobisz
        32225 | Ruth T. McNulty
        33127 | Darlene Y. Rodriguez
        40000 | Steve L. Lewis
        44383 | Amy G. Jones
        46495 | Kevin H. Taylor
(10 rows)

See also