The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: People have started moving the logic up front, just like in any other programming language, where we declare things first, lexically, then use them: Both queries will produce the 5 actors with … Continue reading LATERAL is…
#lateral
3 posts
4 Nov 2022
31 Mar 2017
When looking at execution plans in Oracle, we’ll have to do several steps to be able to call the DBMS_XPLAN package functions. In fact, we have to find out the SQL_ID for a given statement first, and only then we can get its plan. I’ve blogged about this previously, here. However, thanks to lateral unnesting, … Continue reading How to…
10 Feb 2017
Sometimes, SQL can just be so beautiful. One of the less mainstream features in SQL is the array type (or nested collections). In fact, it’s so not mainstream that only 2 major databases actually support it: Oracle and PostgreSQL (and HSQLDB and H2 in the Java ecosystem). In PostgreSQL, you can write: Or in Oracle: … Continue reading Beautiful SQL:…