CODEWITHSUNDEEP
sqlpostgresqlsubquerycommon-table-expression
user3814846
user3814846

Reputation:

Which is better CTE or Inner-Selects in PostgreSQL

Given below is a sample of my two different query execution with same result

The select query using inner-select.

   select p.product from
   (
      select * from tbl_a where productid not in (select productid from tbl_b)
   ) p 
   order by p.product

And select query using CTE.

   with cte as 
   (
   select * from tbl_a where productid not in (select productid from tbl_b)
   )
   select product from cte order by product

Upvotes: 0

Views: 468

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

Postgres always materializes CTEs, which means that the CTE version has additional overhead for reading and writing the data. (Note: this is not necessarily true in other databases.) Postgres does not (necessarily) materialize subqueries, so that version should be faster.

A better way to write this query is to dispense with the subquery/CTE entirely:

  select a.product
  from tbl_a a
  where a.productid not in (select productid from tbl_b)
  order by a.product;

(I am ignoring whether not exists or left outer join would actually be better than not in.)

There is not a general rule for using CTEs instead of subqueries when writing queries. It depends on a lot of factors, especially whether the underlying tables are indexed and how many times the CTE would appear in the query.

Upvotes: 1

Related Questions