Subtle SQL differences: IDENTITY columns

As I'm mostly using Oracle, IDENTITY columns were not so important to me up until I started to support them in jOOQ. Then, I found that yet again, there are many differences between various databases in how they handle IDENTITY columns in DDL and in DML. In SQL, there are essentially three orthogonal concepts of … Continue reading Subtle SQL differences: IDENTITY columns

Binary data in SQL, more trivia

Yesterday, I blogged about inlining boolean literals in SQL. This goes on with other data types. Let's look at BLOBs and BINARY data in general. This is also defined in SQL standards, not in SQL 1992, though: <binary string literal> ::=   X <quote> [ <space>... ]   [ { <hexit> [ <space>... ] <hexit> … Continue reading Binary data in SQL, more trivia

SQL and booleans, some trivia

Some trivia about SQL and booleans: SQL 1992 defines three values for a boolean: <truth value> ::= TRUE | FALSE | UNKNOWN True booleans aren't always supported, though. Here's the truth table of boolean support: SQL DialectSupports booleansDB20 (use 1/0 instead)Derbytrue (you can safely use true/false)H2trueHSQLDBtrueIngrestrueMySQLtrueOracle0PostgrestrueSQL Server0SQLite0Sybase ASE0Sybase SQL Anywhere0 Trivia... but nice to know.

The depths of Java: API leak exposed through covariance

Java can be very tricky some times, especially in API design. Let's have a look at a very interesting showcase. jOOQ strongly separates API from implementation. All API is in the org.jooq package, and public. Most implementation is in the org.jooq.impl package and package-private. Only factories and some dedicated base implementations are public. This allows … Continue reading The depths of Java: API leak exposed through covariance

The truth about IN and EXISTS in SQL

Very nice article, finally getting rid of some doubts... (at least for Oracle)