How to use multiple WITH statements in one PostgreSQL query?

Question

I would like to "declare" what are effectively multiple TEMP tables using the WITH statement. The query I am trying to execute is along the lines of:

WITH table_1 AS (
SELECT GENERATE_SERIES('2012-06-29', '2012-07-03', '1 day'::INTERVAL) AS date
)

WITH table_2 AS (
SELECT GENERATE_SERIES('2012-06-30', '2012-07-13', '1 day'::INTERVAL) AS date
)

SELECT * FROM table_1
WHERE date IN table_2

I've read PostgreSQL documentation and researched into using multiple WITH statements and was unable to find an answer.

Solution 1

Per the other comments the second Common Table Expression [CTE] is preceded by a comma not a WITH statement so

WITH cte1 AS (SELECT...)
, cte2 AS (SELECT...)
SELECT *
FROM
    cte1 c1
    INNER JOIN cte2 c2
    ON ........

In terms of your actual query this syntax should work in PostgreSql, Oracle, and sql-server, well the later typically you will proceed WITH with a semicolon (;WTIH), but that is because typically sql-server folks (myself included) don't end previous statements which need to be ended prior to a CTE being defined...

Note however that you had a second syntax issue in regards to your WHERE statement. WHERE date IN table_2 is not valid because you never actually reference a value/column from table_2. I prefer INNER JOIN over IN or Exists so here is a syntax that should work with a JOIN:

WITH table_1 AS (
SELECT GENERATE_SERIES('2012-06-29', '2012-07-03', '1 day'::INTERVAL) AS date
)

, table_2 AS (
SELECT GENERATE_SERIES('2012-06-30', '2012-07-13', '1 day'::INTERVAL) AS date
)

SELECT * 
FROM
     table_1 t1
     INNER JOIN 
     table_2 t2
     ON t1.date = t2.date
;

If you want to keep the way you had it which typically EXISTS would be better than IN but to to use IN you need an actual SELECT statement in your where.

SELECT * 
FROM
     table_1 t1
WHERE t1.date IN (SELECT date FROM table_2);

IN is very problematic when date could potentially be NULL so if you don't want to use a JOIN then I would suggest EXISTS. AS follows:

SELECT * 
FROM
     table_1 t1
WHERE EXISTS (SELECT * FROM table_2 t2 WHERE t2.date = t1.date);

Solution 2

You can also chain your results using WITH statement. Eg:

WITH tab1 as (Your SQL statement),
tab2 as ( SELECT ... FROM tab1 WHERE your filter),
tab3 as ( SELECT ... FROM tab2 WHERE your filter)
SELECT * FROM tab3;

Comments

[Matt]

glad to help. I can't find the article on not using IN but I would strongly suggest using a JOIN or EXISTS over IN. If a null exists in your result set what happens is you will get every record not just the ones you want. It is weird but it is the way most RDBMs work. try checking a search on it, I know the good answer I saw about that was on this site too... anyway, have a good night

[TD1]

Do you have any recommended resources for learning how to use filter?

[Nagev]

The filter in this context is the condition for the WHERE clause, i.e. a SQL boolean expression. You can see details and examples in the documentation and online tutorials. Playing with a test database is a good way to learn.