4 ms·
> There's literally no operation on data that can't in principle be expressed as an SQL query (albeit the construction might be painful and tedious). There are
by branko_d 4y ago
> There's literally no operation on data that can't in principle be expressed as an SQL query (albeit the construction might be painful and tedious).
There are some problems though:
- Single query can't write to multiple tables.
- Single query can't return multiple resultsets.
You can write a stored procedure, but this is no longer "an SQL query", strictly speaking. And once you start writing stored procedures, you are no longer using just SQL, but whatever Ada-inspired procedural extension to SQL was implemented by the database vendor. In other words, you are using "a traditional programming language to work with the data".
- feike 4y agoA single query can write to multiple tables, using CTE's in PostgreSQL for example. You could compose a SQL query that allows you to map multiple resultsets to 1 resultset, although that feels a bit awkward. WITH a AS ( insert into a (k, v) values ('a', 1.0) returning * ), b AS ( insert into b (k, v) values ('b', 2.0) returning * ) SELECT row_to_json(a) FROM a UNION ALL SELECT row_to_json(b) FROM b; Returns: row_to_json -------------------------- {"a_id":1,"k":"a","v":1} {"b_id":1,"k":"b","v":2} (2 rows)
- branko_d 4y agoGood to know, I wasn't aware PostgreSQL supports this. I'm currently on SQL Server and it doesn't support INSERT as a CTE (and I think most DBMSes out there still don't). It would definitely make my life easier if it did...
- chrisjc 4y agoIt's not the only DB or way of doing it either. Snowflake supports multi-table inserts for example: https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table.html https://docs.snowflake.com/en/sql-reference/sql/insert-multi...
- zasdffaa 4y agoThat is eye-opening but - if it actually works and I find it hard to believe - is no way ... ah, the insert is a CTE because it produces a value ('returning' I guess). Hmm. This is very odd. Doesn't seem to work in mssql. Well thanks for the can of worms...
- ltbarcly3 4y agomssql CTE support is very very basic, to the point of being not very useful.
- zasdffaa 4y agoWhat the hell are you talking about?
- ltbarcly3 4y agoYou can only select, you can't nest cte, the query planner has no understanding of joins that cross cte boundaries so they are totally unoptimized, you can't use distinct or group by or etcetc. Really the CTE implementation in SqlServer is basically a parser level hack.
- zasdffaa 4y ago> You can only select with x as (...) update x set ... > you can't nest cte ok but you can linearise them with x as (...), y as (...) > the query planner has no understanding of joins that cross cte boundaries so they are totally unoptimized utter, reeking garbage. > you can't use distinct or group more garbage. I have. Show me an example of it not working. (Edited for less rudeness)
- ltbarcly3 4y agoso you can do this? WITH t AS ( DELETE FROM foo ) DELETE FROM bar; Or can you only use SELECT for WITH queries? Did you not realize that other databases and the SQL standard allow you to do this? Yes on the final query of the CTE you can do all sorts of things, but that's way less useful if you can't do them in all the component queries.