There are some things in SQL that we simply take for granted without thinking about them properly. One of these things are the GROUP BY and the less popular HAVING clauses. Let's look at a simple example. For this example, we'll reiterate the example database we've seen in this previous article about the awesome LEAD(), … Continue reading Do You Really Understand SQL’s GROUP BY and HAVING clauses?
Tag: sql 1992
Row value expressions and the BETWEEN predicate
Now this is a simple example of how SQL clause simulation can get nasty if you want to make use of some more advanced SQL clauses that aren't supported in all databases. Consider the following predicate and equivalent transformations thereof: The BETWEEN predicate The BETWEEN predicate is a convenient form of expressing the fact that … Continue reading Row value expressions and the BETWEEN predicate
Row value expressions and the NULL predicate
Row value expressions are something very powerful in SQL. They have been around since the early days of standard SQL, e.g. in SQL 1992, even if not all databases implement them correctly, still today. As always, the NULL predicate is one that is a bit tricky to understand, also in the context of row value … Continue reading Row value expressions and the NULL predicate
SQL incompatibilities: NOT IN and NULL values
This is something where many hours of debugging have been spent in the lives of many SQL developers. The various situations where you can have NULL values in NOT IN predicates and anti-joins. Here's a typical situation: with data as ( select 1 as id from dual union all select 2 as id from dual … Continue reading SQL incompatibilities: NOT IN and NULL values