SQL Server supports transforming flat tabular SQL result sets into hierarchical structures by convention using the convenient FOR XML or FOR JSON syntaxes. This is really convenient and less verbose than the standard SQL/XML or SQL/JSON APIs - although the standard ones are more powerful. In this blog post, I'd like to show a few … Continue reading Using SQL Server FOR XML and FOR JSON Syntax on Other RDBMS With jOOQ
Category: sql
This category contains posts about SQL in general or some RDBMS’s SQL dialect
The Many Flavours of the Arcane SQL MERGE Statement
The SQL MERGE statement is a device whose mystery is only exceeded by its power. A simple example shows its full power according to standard SQL. Imagine you have a production table for product prices, and a staging table from which you want to load the latest prices. For once, I'm using the Db2 LuW … Continue reading The Many Flavours of the Arcane SQL MERGE Statement
5 Ways to Better Understand SQL by Adding Optional Parentheses
It appears that our recent beginner SQL articles explaining SQL syntax were quite popular. These include: A Beginner’s Guide to the True Order of SQL Operations A Probably Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL 10 Easy Steps to a Complete Understanding of SQL How SQL DISTINCT and ORDER … Continue reading 5 Ways to Better Understand SQL by Adding Optional Parentheses
SQL DISTINCT is not a function
A very common misconception I often encounter with SQL users is the idea that DISTINCT is something like a function, and that it can take parenthesised arguments. Just recently, I've seen this Stack Overflow question where the OP was looking for a way to express this in jOOQ: SELECT DISTINCT (emp.id), emp.fname, emp.name FROM employee … Continue reading SQL DISTINCT is not a function
Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead
It's been a while since I've ranted on this blog, but I was recently challenged by a reddit thread to write about this topic, so here goes... So, you're writing a service that produces some JSON from your database model. What do you need? Let's see: Read a book on DDD Read another book on … Continue reading Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead
A Guide to SQL Naming Conventions
One of Java's big strengths, in my opinion, is the fact that most naming conventions have been established by the creators of the language. For example: Class names are in PascalCase Member names are in camelCase Constants are in SNAKE_CASE If someone does not adhere to these conventions, the resulting code quickly looks non-idiomatic. What … Continue reading A Guide to SQL Naming Conventions
Dogfooding in Product Development
Dogfooding, or eating your own dog food, is a practice that all product developers should implement all the time. According to wikipedia: Dogfooding, occurs when an organization uses its own product. This can be a way for an organization to test its products in real-world usage. Hence dogfooding can act as quality control, and eventually … Continue reading Dogfooding in Product Development
How to Map MySQL’s TINYINT(1) to Boolean in jOOQ
MySQL 8 does not yet support the BOOLEAN type as specified in the SQL standard. There is a DDL "type" called BOOL, which is just an alias for TINYINT: create table t(b bool); select table_name, column_name, data_type, column_type from information_schema.columns where table_name = 't'; The above produces: TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE| ----------|-----------|---------|-----------| t |b |tinyint |tinyint(1) | Notice … Continue reading How to Map MySQL’s TINYINT(1) to Boolean in jOOQ
What’s Faster? COUNT(*) or COUNT(1)?
One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there's really no reason at all why one should be faster than the other. But is the myth justified? Let's measure! How does COUNT(...) work? But … Continue reading What’s Faster? COUNT(*) or COUNT(1)?
Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER
Using the right data type for some calculation sounds like some obvious advice. There are many blogs about using temporal data types for temporal data, instead of strings. An obvious reason is data integrity and correctness. We don't gain much in storing dates as 2019-09-10 in one record, and as Nov 10, 2019 in the … Continue reading Oracle’s BINARY_DOUBLE Can Be Much Faster Than NUMBER