To have multiple WITH clauses, you do not need to specify WITH multiple times. Rather, after the first WITH clause is completed, add a comma, then you can specify the next clause by starting with <query_name> followed by AS. There is no comma between the final WITH clause and the main SQL query.
You can write multiple psql commands and SQL statements in one text file (say, named statements. sql ), and then use the command psql congress -af statements. sql to execute them all. Use “ ; ” to signal the end of each SQL statement in the file.
In PostgreSQL, the WITH query provides a way to write auxiliary statements for use in a larger query. It helps in breaking down complicated and large queries into simpler forms, which are easily readable.
In PostgreSQL, the CTE(Common Table Expression) is used as a temporary result set that the user can reference within another SQL statement like SELECT, INSERT, UPDATE or DELETE. CTEs are temporary in the sense that they only exist during the execution of the query.
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);
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With