SQL clauses WHERE and HAVING have different functions. But they both filter out rows, so many people don’t know the important difference between them. Let’s shred some light.
Get a running sum in SQL
Running sums are useful in situations where you want to get a total, but you also want to see how much each entry of a list contributes to the total. So each entry is accompanied by a partial sum, up to that point of the list. While getting a total in SQL is trivial, getting a running sum requires the use of window functions.
PostgreSQL: Deleting or re-ordering ENUM elements with no disruption
PostgreSQL manual says that it is not possible to delete an element from an ENUM type, nor change their order. However, there is a way to do it in a single transaction.
Select values that occur multiple times
There are many cases when we want to select values that occur multiple times: customers that ordered the same product multiple times, users with the same email, and so on. We can do this with GROUP BY and HAVING.
Db2: Working with aliases
Let’s see how to create, modify and use an alias in Db2. An alias can refer to a table, a sequence or a module. Aliases exist in several databases, but we’re focusing on Db2 syntax here.
SQL Server: CHECKSUM() vs CHECKSUM_BINARY() vs HASHBYTES()
SQL Server has at least three functions to create checksums or hashes: CHECKSUM(), CHECKSUM_BINARY(), and HASHBYTES(). Here we discuss what differences exist between them, and how to choose the proper function for a specific use case.
Informix: Select NULL explicitly
Sometimes we want to extract a literal value with a SELECT. This works perfectly fine if the literal value is 1 or 'a', but it fails with an error if the value is NULL. Let’s discuss why and how to solve the problem.
MariaDB: Running a prepared statement in a stored procedure
How to run prepared statements in a MariaDB stored procedure. The article covers one-off prepared statements, reusable prepared statements, and parameters.
PostgreSQL: Working with ENUM types
Sometimes a text column should not contain free texts, but only values from a predefined list. PostgreSQL allows to achieve this by creating an enumeration – or an ENUM type. This allows validation on input, and makes the values more efficient.
Return missing combinations of items from different tables
How to find out which combinations of items from different tables never appear in a third table.