Using IGNORE NULLS With SQL Window Functions to Fill Gaps

I found a very interesting SQL question on Twitter recently: https://twitter.com/vikkiarul/status/1120669222672261120 Rephrasing the question: We have a set of sparse data points: +------------+-------+ | VALUE_DATE | VALUE | +------------+-------+ | 2019-01-01 | 100 | | 2019-01-02 | 120 | | 2019-01-05 | 125 | | 2019-01-06 | 128 | | 2019-01-10 | 130 | +------------+-------+ … Continue reading Using IGNORE NULLS With SQL Window Functions to Fill Gaps

Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL

One of the most wanted features in the Oracle database is the BOOLEAN type. The SQL standard specified it a while ago, and RDBMS like PostgreSQL show how powerful it can be, e.g. when using the EVERY() aggregate function. The PL/SQL language already has support for boolean types. We can write: CREATE OR REPLACE FUNCTION … Continue reading Calling an Oracle Function with PL/SQL BOOLEAN Type from SQL

The Difference Between SQL’s JOIN .. ON Clause and the Where Clause

A question that is frequently occurring among my SQL training's participants is: What's the difference between putting a predicate in the JOIN .. ON clause and the WHERE clause? I can definitely see how that's confusing some people, as there seems to be no difference at first sight, when running queries like these, e.g. in … Continue reading The Difference Between SQL’s JOIN .. ON Clause and the Where Clause