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…
#apply
3 posts
4 Nov 2022
15 Aug 2018
Oracle is one of the few databases that implements the SQL standard ORDBMS extensions, which essentially allow for nested collections. Other databases that have these features to some extent are CUBRID, Informix, PostgreSQL. Oracle has two types of nested collections: The main difference at first is that a nested table can be of arbitrary size, … Continue reading How to…
22 May 2017
There are some situations where you would like to have at least one (empty) row in your result set in SQL. Imagine the following situation. We’re querying the Sakila database for actors and their films: yielding something like: +------------+-----------+---------------------+ | FIRST_NAME | LAST_NAME | TITLE | +------------+-----------+---------------------+ | ... | ... | ... | | … Continue reading How to…