0

Is there anyway to do this? I'm trying to do something like this:

SELECT x
FROM table
EXCEPT
WITH RECURSIVE ...();
5

The WITH goes first in a query. You can use this:

WITH RECURSIVE ct AS (...)
SELECT x
FROM table
EXCEPT
SELECT y
FROM ct ;

For the record, CTEs can be (in standard SQL) inside subqueries but not many DBMS have implemented this syntax:

SELECT x
FROM table
EXCEPT
SELECT y
FROM 
  ( WITH RECURSIVE ct AS (...)
    SELECT y
    FROM ct 
  ) AS c ;

or this simpler one:

SELECT x
FROM table
EXCEPT
( WITH RECURSIVE ct AS (...)
  SELECT y
  FROM ct 
) ;
  • 1
    @CL. what do you mean "you are not allowed to use subqueries directly"? – ypercubeᵀᴹ Jan 23 '17 at 13:46
  • 2
    @CL.: neither of those examples uses a "sub-query". Those are derived tables - which is something different then a sub-query. Most importantly a derived table can be used anywhere a regular table can be used. All three queries are perfectly valid by the SQL standard (apart from the missing alias for the derived table if I'm not mistaken) – a_horse_with_no_name Jan 23 '17 at 14:02
  • 1
    CL. I don't understand what you mean with subquery then. Both (select * from t) except (select * from t); and select * from t except select * from t; are valid queries, as far as I know. – ypercubeᵀᴹ Jan 23 '17 at 14:07
  • 1
    @a_horse_with_no_name all the 3 I have in my answer run fine in Postgres. – ypercubeᵀᴹ Jan 23 '17 at 14:16
  • 2
    @CL Skimming the SQL-99 standard (old, but still relevant), non-join query expression (which defines UNION/EXCEPT) can contain parenthesized queries as either operand: non-join query expression can be composed of non-join query primary containing left-paren non-join query expression right-paren then work back down from non-join query expression to query specification via non-join query term. Looks like valid standard SQL to me (not to mention it works happily in many engines which is probably a more useful yardstick ;) – Dave Jones Jan 24 '17 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.