3 ms·
Knew most/all of these tricks already, but it's a good summary of some useful tricks. Just one thing... "Common Table Expressions (also: CTE, also referred to
by ZenoArrow 11y ago
Knew most/all of these tricks already, but it's a good summary of some useful tricks.
Just one thing...
"Common Table Expressions (also: CTE, also referred to as subquery factoring, e.g. in Oracle) are the only way to declare variables in SQL (apart from the obscure WINDOW clause that only PostgreSQL and Sybase SQL Anywhere know)."
Can't say anything about other vendors, but in T-SQL (a.k.a. MS SQL) creating variables is easily done via the DECLARE keyword, e.g. DECLARE @x INT = 4; . You can create a number of structures this way, including temporary tables (there are few different types of temporary tables, but the ones specified with DECLARE are specific to the scope in which they were defined in).
Does the DECLARE keyword (or something similar) not exist within any of the SQL standards?
- pohart 11y agoCTEs aren't for creating a simple value. They are for creating a table variable.
- ZenoArrow 11y agoI know what CTEs are for (in practical terms they're useful for subqueries you need to reference more than once), and like I said before, you can define temporary tables with the DECLARE keyword. To give you an example, this is valid T-SQL code... DECLARE @temptable TABLE (id INT, fulldescription NVARCHAR(MAX));