SQL WITH clause doesn't work

Question

I'm trying to execute seemingly simple request contains WITH clause:

WITH sub AS (SELECT url FROM site WHERE id = 15) 
SELECT * FROM search_result WHERE url = sub.url

But it doesn't work. I get

ERROR: missing FROM-clause entry for table "sub"

What's the matter?

@Ryan Gates Yes, `WITH` clause is required to use. My real query is much more complex and confusing. So I'm trying to execute this simple request to understand how it should be used and what is wrong with it. — Alex, May 28 '13 at 18:50
What is the difference between using the `WITH` clause and an inner join for the logic in your question? Or logic that you have omitted? — Ryan Gates, May 28 '13 at 19:12
Logic is that the `SELECT` with `WITH` statement performed once. — Alex, May 28 '13 at 19:22

score 1 · Accepted Answer · answered May 28 '13 at 17:46

Table expressions need to be used like tables. You're trying to use the value of sub as a scalar.

Try this (forgive me, Postgres is not my first SQL dialect).

WITH sub AS (SELECT url FROM site WHERE id = 15) 
SELECT * FROM sub
INNER JOIN
 search_result 
ON
  sub.url = search_result.url

EDIT, alternatively, you could just skip the WITH clause and go with:-

SELECT * FROM 
  site
INNER JOIN
  search_result 
ON
  site.url = search_result.url
WHERE 
  site.id = 15

Erwin Brandstetter · Answer 2 · 2013-05-29T13:47:35.963

Don't use a CTE at all for this simple case. Unlike you seem to be expecting, the following simple query without a CTE will be slightly faster:

SELECT r.*
FROM   search_result r
JOIN   site s USING (url)
WHERE  s.id = 15;

Test with EXPLAIN ANALYZE to verify.

CTEs introduce an optimization barrier. They have many very good uses, but they won't make simple queries faster. Here is a thread on pgsql-performance that gives you more details as to why that is.

score 0 · Answer 3 · answered May 28 '13 at 17:47

0

That's not the correct way to use a CTE:

With sub as (
    SELECT url
    FROM site
    WHERE id = 15
)
SELECT * 
FROM Search_Result SR 
   JOIN sub ON SR.url = sub.Url

answered May 28 '13 at 17:47

sgeddes

62,311
6
61
83

score 0 · Answer 4 · answered May 28 '13 at 17:55

You can just as easily do an inner join:

SELECT search_result .* 
FROM
    search_result
INNER JOIN
    (SELECT url FROM site WHERE id = 15) as st
ON
    search_result.url = st.url

This does the filtering so that you are joining on a smaller set than if you did the where clause outside of the filtering. This may not matter in your case, but it is something to consider.

SQL WITH clause doesn't work

4 Answers4