When using a CTE, why do I need to use SELECT twice in order to filter on values from an auxiliary statement?

Question

  WITH aux_results AS (
    SELECT id
    FROM vendors
  )
  SELECT c.id
  FROM customers AS c
  WHERE c.vendor IN (SELECT * FROM aux_results);

Here is a naive and simple example of a CTE. The auxiliary statement uses SELECT to retrieve ids. To filter on them I have to use SELECT or an exception is thrown.

Why is this, Why is it a different pattern than using a subquery to make the same overall query?

See:

https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-IN

It appears that the object returned by a subquery is different than the object returned by the CTE auxiliary statement. The way you must query is different, therefore the outer interface is different, yet they both could be considered transient tables.

As an aside, is there any performance hit? (doubt it) Should I just use a subquery? (doubt it)

Answer 1

is there any performance hit? (doubt it) Should I just use a subquery? (doubt it)

Typically, IN is sub-optimal for this type of query. It makes Postgres - or any RDBMS for that matter - (try to) fold duplicates in the set to the right. Chances are, there are no dupes coming from a query like SELECT id FROM vendors, then that's a wasted effort, and a plain join will be faster. (You'll see a different query plan, too.)

WITH aux_results AS (
   SELECT id
   FROM   vendors
   )
SELECT c.id
FROM   aux_results a
JOIN   customers c ON c.vendor = a.id;

Since Postgres 12, a pointless CTE will be inlined and hardly incur any performance penalty (a minor expense in the planning stage). See:

• Does WITH query store the results of referred tables?

A subquery is at least as fast or faster for simple cases. Shorter, too:

SELECT c.id
FROM   vendors v
JOIN   customers c ON c.vendor = v.id;