4 ms·
Worth noting that this isn't all ANSI-SQL... e.g. I'm pretty sure WITH is a Postgres thing?
by mosburger 6y ago
Worth noting that this isn't all ANSI-SQL... e.g. I'm pretty sure WITH is a Postgres thing?
- Minor49er 6y agoNot anymore. Other implementations, like MariaDB and SQLite, have adopted common table expressions. They're basically syntactic sugar for subqueries in most implementations, but they can make some queries much more readable
- greggyb 6y agoCTEs are implemented by most (all?) major RDBMS platforms and were introduced in the SQL:1999 standard revision. - SQL:1999 https://en.wikipedia.org/wiki/SQL:1999#Common_table_expressions_and_recursive_queries https://en.wikipedia.org/wiki/SQL:1999#Common_table_expressi... - SQL standardization https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization https://en.wikipedia.org/wiki/SQL#Interoperability_and_stand...
- combatentropy 6y agoThe WITH clause, otherwise known as Common Table Expressions, is in ANSI SQL99. Common table expressions are supported by all of the major databases: PostgreSQL, Microsoft, Oracle, MySQL, MariaDB, and SQLite. They are also in certain minor ones, like Teradata, DB2, Firebird, and HyperSQL. Recursive WITH clauses are especially useful.
- matwood 6y agoWorth noting that MySQL only got CTE's in 8.0 while something like MSSQL has had them since ~2005. The reason this is important is that something like AWS Aurora only supports up to MySQL 5.7, and thus no CTEs. :/
- maxlamb 6y agothe WITH clause is now in SQL Server and Oracle