The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: SELECT actor_id, name, COUNT(*) FROM ( SELECT actor_id, first_name || ' ' || last_name AS name FROM actor ) AS a JOIN film_actor AS fa USING (actor_id) GROUP BY actor_id, name ORDER … Continue reading LATERAL is Your Friend to Create Local Column Variables in SQL
Tag: Lateral Derived Table
Add LATERAL Joins or CROSS APPLY to Your SQL Tool Chain
The T-SQL dialect has known the powerful CROSS APPLY and OUTER APPLY JOIN syntaxes for ages. The SQL:1999 standard had introduced almost equivalent "lateral derived tables", which are finally supported with PostgreSQL 9.3, or Oracle 12c, which has adopted both the SQL standard LATERAL syntax and the T-SQL vendor-specific CROSS APPLY and OUTER APPLY syntaxes. … Continue reading Add LATERAL Joins or CROSS APPLY to Your SQL Tool Chain