Are SELECT type queries the only type that can be nested?

by

Basic answer

There are CTEs (Common Table Expressions) in Postgres – like in any major modern RDBMS, That includes MySQL since version 8.0.
Since version 9.1 Postgres also features data-modifying CTEs, which can be “nested”.

Unlike subqueries CTEs pose as optimization barriers. The query planner cannot inline trivial commands into the main command or reorder joins among main query and CTEs. The same is possible with subqueries. May be (very) good or (very) bad for performance, it depends.
Either way, CTEs require a bit more overhead than subqueries.

Update: Postgres 12 can finally inline plain CTEs in the main query.

Details you did not ask for

Your question is very basic, the above is probably enough to answer. But I’ll add a bit for advanced users (and a code example to show the syntax).

All CTEs of a query are based off the same snapshot of the database. The next CTE can reuse the output of previous CTEs (internal temporary tables), but effects on underlying tables are invisible for other CTEs. The sequence of multiple CTEs is arbitrary unless something is returned with the RETURNING clause for INSERT, UPDATE, DELETE – irrelevant for SELECT, since it does not change anything and just reads from the snapshot.

That can have subtle effects with multiple updates that would be affecting the same row. Only one update can affect each row. Which one is influenced by the sequence of CTEs.

Try to predict the outcome:

CREATE TEMP TABLE t (t_id int, txt text);
INSERT INTO t VALUES (1, 'foo'), (2, 'bar'), (3, 'baz');

WITH sel AS (SELECT * FROM t)
   , up1 AS (UPDATE t SET txt = txt || '1' WHERE t_id = 1 RETURNING *)
   , up2 AS (UPDATE t SET txt = t.txt || '2'
             FROM   up1
             WHERE  up1.t_id = t.t_id
             RETURNING t.*)
   , ins AS (INSERT INTO t VALUES (4, 'bamm'))
   , up3 AS (UPDATE t SET txt = txt || '3' RETURNING *)
SELECT 'sel' AS source, * FROM sel
UNION ALL
SELECT 'up1' AS source, * FROM up1
UNION ALL
SELECT 'up2' AS source, * FROM up2
UNION ALL
SELECT 'up3' AS source, * FROM up3
UNION ALL
SELECT 't'   AS source, * FROM t;

db<>fiddle here
Old sqlfiddle

Don’t be disappointed, I doubt there are many here who could have done it. 🙂
The gist of it: avoid conflicting commands in CTEs.

More Related Contents:

  1. How to filter SQL results in a has-many-through relation
  2. How to select the nth row in a SQL database table?
  3. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  4. MySQL “WITH” clause
  5. Simple Random Samples from a Sql database
  6. SQL: Repeat a result row multiple times, and number the rows
  7. Why do results from a SQL query not come back in the order I expect?
  8. Best practices for SQL varchar column length [closed]
  9. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  10. how to cast the hexadecimal to varchar(datetime)?
  11. Postgres LEFT JOIN with WHERE condition
  12. Syntax error: WITH is not valid input in this position
  13. What happens when I exhaust a bigint generated key? How to handle it?
  14. Equivalent of explode() to work with strings in MySQL
  15. How can I tell when a MySQL table was last updated?
  16. MySQL DELETE FROM with subquery as condition
  17. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  18. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  19. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  20. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  21. Determine Rank based on Multiple Columns in MySQL
  22. Checking multiple columns for one value
  23. What is better in MYSQL count(*) or count(1)?
  24. Query that ignore the spaces
  25. How to enable INNODB in mysql
  26. Bash script to insert values in MySQL
  27. MySQL GROUP BY DateTime +/- 3 seconds
  28. How to regex in a MySQL query
  29. Syntax error on MERGE statement
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment